SQL_CALC_FOUND_ROWS - Is it faster?
Reading through a post on SQL_CALC_FOUND_ROWS actually being slower that running one query followed by a count query I decided I needed to do a little benchmarking on my own.
Creating the sample data
For this I wanted a relatively large data set to work with to make the difference as high as possible. I created a table with 3 fields - id int(11), stringvalue varchar(50), and intvalue int(11). To fill the table I used the following function borrowing the createRandomPassword() function from TotallyPHP with a slight modification so I could generate random strings of differing lengths.
-
<?php
-
-
for ($i=0; $i<100000; $i++)
-
{
-
}
-
-
function createRandomPassword($maxLength=7)
-
{
-
$chars = “abcdefghijkmnopqrstuvwxyz023456789″;
-
-
$i = 0;
-
-
$pass = ” ;
-
-
while ($i <= $maxLength)
-
{
-
$pass = $pass . $tmp;
-
$i++;
-
}
-
return $pass;
-
}
-
?>
Each record was filled with a random string from 10 to 50 characters and a random number between 5 and 100,000.
The benchmarking code
I’m starting with the benchmarking script I wrote about earlier. It seems to be fairly easy to use and get data quickly. That post is just a framework though. I’ll post the full code I’m using a little later.
A note about the times
Any time I’m going to list is an average of 10 tests with each test being 1,000 cycles of the queries listed. So if you want to see the real time difference you’ll need to divide each number by 1,000.
Queries
These 3 sets of queries are the 3 that I’m going to use.
Query 1
-
SELECT * FROM benchmark ORDER BY intvalue ASC LIMIT 50,10;
-
SELECT COUNT(id) FROM benchmark
Query 2
-
SELECT SQL_NO_CACHE * FROM benchmark ORDER BY intvalue ASC LIMIT 50, 10
-
SELECT COUNT(id) FROM benchmark
Query 3
-
SELECT SQL_CALC_FOUND_ROWS * FROM benchmark ORDER BY intvalue ASC LIMIT 50, 10
-
SELECT FOUND_ROWS()
First Run - No indexes
For the first run through the 3 query sets SQL_CALC_FOUND_ROWS came out the fastest. The 1,000 cycles averaged 0.814143 seconds compared to 0.8433455 seconds for query 1 and 0.8869967 seconds for query 2.
Second Run - Index added on intvalue field
This time query 2 came out ahead at 0.667846 seconds. SQL_CALC_FOUND_ROWS was second at 0.698917 seconds and query 1 came in last at 0.798549 seconds.
Conclusion
It’s such a minor difference that I don’t see myself going back and changing any queries that I’ve written previously, and I probably won’t change from using SQL_CALC_FOUND_ROWS because it just seems easier to me. For the indexed test the difference in a single set of queries is only 1/100,000th of a second. I supposed that could add up when you’re running it thousands of times a minute, but I would assume at that point there is probably more you can do to shave off some time.
Room for improvement?
After reading the post I linked to earlier I thought the difference would be much higher and I wanted to make sure that I was using the right technique, and I’m fairly confident that I am.
If I were to run more tests along these lines I would probably do so with a larger table, both in terms of fields and total records. But to be more useful, more testing should probably be done against a database table that I’m using in a project rather than just a sample like these tests.
Question, Comments...
Do you have more questions. Please either leave a comment below or join us in our new forum.