CODEWITHSUNDEEP
mysqlsqljoinwhere-clause
John
John

Reputation: 3

mysql performance difference between where id = vs where id IN()

I am very new to mysql . I had a query, from this query i am getting some stock_ids.In the second query i need to pass those stock_ids. For this i am fetching all the stock_ids in a array.Now my question is in which way can i pass those stock_ids to the second query. For this i have two approaches.

First approach:
$array_cnt = count($stockid_array);
for($i = 0; $i<$array_cnt;$i++)
{
$sql = "select reciever_id,sender_identifier,unique_stock_id,vat_number,tax_number from stocktable where stock_id = '".$stockid_array[$i]."'";
// my while loop
}
Another approach is 

$sql = "reciever_id,sender_identifier,unique_stock_id,vat_number,tax_number from stocktable where stock_id in ('1','2','3','4','5','6','7','8','9');
//while loop comes here.

So which approach gives good performance ? Please guide me.

Upvotes: 0

Views: 509

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269743

MySQL has a nice optimization for constants in an in list -- it basically sorts the values and uses a binary search. That means that the in is going to be faster. Also, in can take advantage of an index on the column.

In addition, running a single query should be faster than running multiple queries.

So, the in version should be better than running multiple queries with =.

Upvotes: 2

Related Questions