Pagination MYSQL count vs PHP count and array_slice

0
votes

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?

2
phpmysqlpdopagination

2 Answers

0
votes

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.

0
votes

Hi can i ask which is the best method? I am looking at this issue now and trying to find the best way to speed up.

I am currently using 

// Fetch All Rows 
$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 am not able to find out the performance of array_slice so not sure if it is the best method or should I revert back to especially for large dataset

// Fetch Count of all rows for pagination
SELECT COUNT(id) FROM table WHERE ...

// Fetch the require rows
SELECT id, name FROM table WHERE ...