More optimised way to do pagination queries

Wildhoney · 09-06-2007, 03:01 PM

Many people use the following queries to work out the pagination totals. I've seen it on Pixel2Life as well. However, few of us are aware that an ALL query on a database is bad. This is the process by which MySQL scans the entire table just to gather all the data. Every single row and possibly every single column.

There is, however, a better way.

SQL_CALC_FOUND_ROWS is 1 of many flags you are able to set at the beginning of the SELECT query.

Code:

SELECT
	SQL_CALC_FOUND_ROWS
	myColumn1,
	myColumn2
FROM
	myTable
LIMIT
	0, 10

This will bring back myColumn1 and myColumn2 from myTable, but it will also count the entire amount of items from the table. I'm sure many are aware that any COUNT on a table without any WHERE clause or HAVING clause gets the information straight from the table's attached information thus preventing the killer ALL query.

The aforementioned query will list the first 10 items from your table. However, for the pagination section you will want the total amount of rows to be able to compile the pagination itself. IE:

< Previous - 1, 2, 3, 4, 5 - Next >

Now, as we've used SQL_CALC_FOUND_ROWS we are able to acquire the total amount of rows regardless of the LIMIT clause we attached to our earlier query. To get the value from that SQL_CALC_FOUND_ROWS we use the following MySQL query:

Code:

SELECT FOUND_ROWS();

Therefore, as we limited our query to 0 to 10 columns, we can use the above query to get the total amount of rows. So, if there were 455 rows in our table as reference in the first query, the above query would return 455.

Now how's about that for MySQL optimisation?

jordie · 09-13-2007, 09:51 AM

I agree its rather handy, and I've been actually using it in all my scripts lately, however I did find its not always the fastest. Sometimes doing 2 queries: a

Code:

select count(ID) from blah where visible=1

and the

Code:

select field,anotherfield from blah where visible=1 limit 0, 10

can actually be faster.

I couldn't narrow down why this would be but it was a matter of 0.02 seconds faster on a database of 5,000 entries.

Though of course, it is MUCH faster, and less of a load on MySQL when performing searches using fulltext indexes with match() against(). This is really handy when you need to paginate your search results. :)

Wildhoney · 09-13-2007, 11:40 AM

You can't beat MATCH and AGAINST. But on your count query you have a WHERE clause which you really shouldn't. Especially if your table is that large as it would slow the query down considerably. Have you not noticed any adverse effects up to now? I know for a fact that a COUNT(*) without a WHERE clause extracts the data straight from the table's information and therefore is super quick.

jordie · 09-13-2007, 12:35 PM

I do understand that, but the data does need to be filtered. For example, I only need to count the number of items that are marked as visible. Just doing a count(*) on the entire table without restricting what rows to count is rather meaningless in most of the situations I require it... Unless you're aware of a better method? :S

Wildhoney · 09-13-2007, 02:10 PM

Not a good method, admittedly, as it'll inflate the size of the index file somewhat. However, you can speed the query up considerably by making you visible column an index.

Based on the following query:

Code:

EXPLAIN SELECT
	COUNT(*)
FROM
	myTable
WHERE
	myColumn2 > 0

Where myColumn2 is an index, the query is improved by making the query an index query instead of the dreaded ALL query.

I might also be tempted to place all the visible rows into a new table. However, that is probably overkill!