TalkPHP
 
 
Account Login
Latest Articles
» The basic usage of PHPTAL, a XML/XHTML template library for PHP
» Vulnerable methods and the areas they are commonly trusted in.
» Simple way to protect a form from bot
» The Basics On: How Session Stealing Works
» How to keep your forms from double posting data
Advertisement
Associates
Associates
techtuts Darkmindz
CSS Tutorials Tutorialsphere.com - Free Online Tutorials
Boston PHP SurfnLearn
Reply
 
LinkBack Thread Tools Display Modes
Old 09-06-2007, 03:01 PM   #1 (permalink)
La Vida es Sueño
Advanced Programmer Top Contributor 
 
Wildhoney's Avatar
 
Join Date: Sep 2007
Location: Oldham
Posts: 1,547
Thanks: 72
Wildhoney is on a distinguished road
Wink More optimised way to do pagination queries

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?
__________________
The man who comes back through the Door in the Wall will never be quite the same as the man who went out.
Send a message via AIM to Wildhoney Send a message via MSN to Wildhoney Send a message via Yahoo to Wildhoney
Wildhoney is offline  
Reply With Quote
Old 09-13-2007, 09:51 AM   #2 (permalink)
The Wanderer
 
Join Date: Sep 2007
Location: Sydney, Australia
Posts: 19
Thanks: 0
jordie is on a distinguished road
Default

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. :)
jordie is offline  
Reply With Quote
Old 09-13-2007, 11:40 AM   #3 (permalink)
La Vida es Sueño
Advanced Programmer Top Contributor 
 
Wildhoney's Avatar
 
Join Date: Sep 2007
Location: Oldham
Posts: 1,547
Thanks: 72
Wildhoney is on a distinguished road
Default

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.
__________________
The man who comes back through the Door in the Wall will never be quite the same as the man who went out.
Send a message via AIM to Wildhoney Send a message via MSN to Wildhoney Send a message via Yahoo to Wildhoney
Wildhoney is offline  
Reply With Quote
Old 09-13-2007, 12:35 PM   #4 (permalink)
The Wanderer
 
Join Date: Sep 2007
Location: Sydney, Australia
Posts: 19
Thanks: 0
jordie is on a distinguished road
Default

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
jordie is offline  
Reply With Quote
Old 09-13-2007, 02:10 PM   #5 (permalink)
La Vida es Sueño
Advanced Programmer Top Contributor 
 
Wildhoney's Avatar
 
Join Date: Sep 2007
Location: Oldham
Posts: 1,547
Thanks: 72
Wildhoney is on a distinguished road
Default

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!
__________________
The man who comes back through the Door in the Wall will never be quite the same as the man who went out.
Send a message via AIM to Wildhoney Send a message via MSN to Wildhoney Send a message via Yahoo to Wildhoney
Wildhoney is offline  
Reply With Quote
Reply



Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


All times are GMT. The time now is 05:54 AM.

 
     

Powered by vBulletin® Version 3.6.8
Copyright ©2000 - 2008, Jelsoft Enterprises Ltd.
Search Engine Optimization by vBSEO 3.1.0