I have a search query which looks for matching values from a search table and returns all possible entries matching the keywords. I did not write the search, it’s something that already existed and we’re porting it to another framework. Anyway, that’s not the point.
What I want is to execute a MySQL query (InnoDB), using LIMIT
, and have the COUNT
of all results at my disposal for pagination (to calculate number of total pages). I assumed using LIMIT
has some effect on the speed but after measuring execution time it doesn’t seem to have much (atleast in this case).
The problem:
Using a separate query to COUNT
rows (just count) takes as much time as the real query. In this case approximately 11 seconds so the entire execution is about 22 seconds.
// Fetch Count approximately 11 seconds SELECT COUNT(id) FROM table WHERE ... // Fetch All approximately 11 seconds SELECT id, name FROM table WHERE ...
Another method I used was to just search without LIMIT
and then use PHP
‘s own count()
function to get the number of results I got, then array_slice()
to limit the number of entries for the page. This actually proved to be faster as the query took about 12 seconds and PHP
a fraction of a second. So it’s about half.
// Fetch All approximately 11-12 seconds $results = SELECT id, name FROM table WHERE ... // PHP count and slice took a fraction of a second $count = count($results); $results = array_slice($results, $page, $limit);
I also tried to use COUNT
inside the first LIMIT
query so I wouldn’t have to use a separate query for it but it seems PDO
‘s own fetchAll
returns variating results in each array entry. What I got was 2, 1, 1, 1…
SELECT COUNT(id) as `count`, id, name FROM table WHERE ...
The question:
What is the fastest way to actually query results for pagination having the results and result count at your disposal?
Advertisement
Answer
Second option is not an option at all.
On the other hand, 11 seconds is still absolutely unacceptable. So, to make your code proper, you have to optimize the search query. This should be your goal and concern.