Query optimization : Which SELECT syntax is faster?

Question

Given 5,000 IDs of records fetch in the database, which query , in your opinion is faster?

1. Loop through 5000 IDs using php and perform a SELECT query for each one,

   foreach($ids as $id){
     // do the query 
     $r = mysql_query("SELECT * FROM TABLE WHERE ID = {$id}");
   }
   

2. Or collect all ids in an array, and use SELECT * FROM TABLE WHERE ID IN (1 up to 5000)

   //assuming $ids = array(1,2 ---- up to 5000);
   $r = mysql_query("SELECT * FROM TABLE WHERE ID IN (".join(",",$ids).")");
   

Answer 1

Without a shadow of a doubt, loading them all in one go will be faster. Running 5,000 queries is going to be a lot slower as each query will carry a certain amount of overhead.

Also, to speed it up even more, DON'T use the * operator! Select the fields you are going to use, if you only need the ID column, specify this! If you want all the columns, specify them all, because you may later add fields in and you do not need to retrieve this new field.

Answer 2

option 2 is definitely going to be faster. 5000 separate db queries are going to have huge network connection overhead.