CODEWITHSUNDEEP
phpsqlmysqloptimization
r2b2
r2b2

Reputation: 1275

Query optimization : Which SELECT syntax is faster?

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

Upvotes: 3

Views: 1137

Answers (7)

Tom Gullen
Tom Gullen

Reputation: 61729

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.

Upvotes: 4

vizzdoom
vizzdoom

Reputation: 736

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';
?>

Upvotes: 0

Yves M.
Yves M.

Reputation: 3318

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.

Upvotes: 0

Rui
Rui

Reputation: 252

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.

Upvotes: 0

Your Common Sense
Your Common Sense

Reputation: 157828

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

Upvotes: 2

Espen Schulstad
Espen Schulstad

Reputation: 2445

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.

Upvotes: 0

Sergey Eremin
Sergey Eremin

Reputation: 11080

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

Upvotes: 4

Related Questions