Query optimization : Which SELECT syntax is faster?

Question

Hello,

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).")");
   

Thanks!

Answer 1

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

Answer 2

Sure measure it, but I'd certainly recommend letting the database doing the job. All depends, I hope you're not creating a connection for each call though.

Answer 3

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 4

The fastest way is not to request 5000 rows at all.
You barely need 100 to display them on one page. 5000 is way overkill

Answer 5

Loop is faster if you use a Query Statement using bind variables. Declare the Statement off the loop; then inside the loop bind the variable per each id.

Do not underestimate the time going into SQL parsing; especially on these long winded things.

Answer 6

Option 2 is faster. With option 1 you do a full roundtrim to the server for each iteration.

I'd point out that in this case you might consider using paging to display the data.

Hint: Measure, Measure, Measure. With a code worth 10 minutes of your time you will have the answer right away.

Answer 7

Which is faster for many queries?
Try measure it for example like this:

<?php
    $start = getmicrotime();
    for ($i=0;$i<100000;$i++)
    {
            foreach($ids as $id){
              // do the query 
              $r = mysql_query("SELECT * FROM TABLE WHERE ID = {$id}");
            }

    }
    $end = getmicrotime();
    echo 'Time (1): '.($end- $start).' sec';



    $start = getmicrotime();
    for ($i=0;$i<100000;$i++)
    {
            //assuming $ids = array(1,2 ---- up to 5000);
            $r = mysql_query("SELECT * FROM TABLE WHERE ID IN (".join(",",$ids).")");
    }
    $end = getmicrotime();
    echo 'Time (2): '.($end- $start).' sec';
?>