More optimised way to do pagination queries

Wildhoney · 09-06-2007, 02: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, 08: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, 10: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, 11:35 AM

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, 01: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!

dashixiong · 10-18-2012, 01:55 PM

Some conservatives have Coach Factory Outlet pushed that critique further, saying that Mr. Obama’s policies are too costly, often assist the wrong people Louis Vuitton Belts and could have the paradoxical effect of driving up college costs. The dispute turns not just on different Coach Factory Outlet assessments of how policies play out, but on differing philosophical views about the role of government. During Gucci Belts his time in office, Mr. Obama has sharply increased aid to low- and middle-income students, notably through the Pell Grant Coach Factory Outlet program, which grew from $14.6 billion given to 6 million students in 2008, to nearly $40 billion for Coach Factory Outlet almost 10 million students this year. His administration also made it easier to request aid, shortening the Coach Factory Online complex federal application and allowing people to transfer their financial information electronically from the Internal Coach Outlet Online Revenue Service database. But while many education experts laud his efforts, analysts of varying political Coach Outlet Online stripes have also questioned how much impact some of the president’s policies will have, noting that the prices Coach Online Outlet charged by colleges, and student borrowing, continue to climb.But behind the headlines about soaring costs, the Coach Factory Outlet Online reality is more complex and wildly uneven, because a growing number of students receive Coach Outlet Online financial aid, and only relatively high-income families pay those fast-rising sticker prices. Adjusted for Coach Factory Online inflation, the College Board calculates, the average net price changed little over the last decade at private Coach Factory Outlet schools, and rose only modestly at public ones.Defending federal spending, Arne Duncan, the secretary of Hermes Belts education, said that for more than 30 years, college prices had risen even when federal aid had not, leading him to believe Coach Factory Online there was zero correlation.

dashixiong · 10-22-2012, 09:34 AM

You’ve relativelyCoach Outlet recently arrived in New Delhi after living in two of Asia’s other great cities,Coach Outlet Store Online Tokyo and Hong Kong, for several years. Do these cities feel like they’re part of the same continent? Yes, and no. In terms Coach Factory Onlineof infrastructure, they couldn’t be more different. Getting regularCoach Outlet power and water at my house in New Delhi is never a sure thing, even though Coach Purse Outlet OnlineI’m paying the same rent that I paid in Tokyo and almost the same electricity prices. Both Hong Kong and Tokyo are also crowded places,Coach Factory Outlet Online but both cities are incredibly well planned and efficiently run. Efficient is not a word I would use to describe my Coach Bags Outlet Onlineday-to-day life in New Delhi. On the other hand, one thing that I think Hong Kong and New Delhi have in common isCoach Handbags Outlet a shared sense of optimism — a feeling that the best is yet to come. That’s definitely not the feeling you get in Tokyo,Coach Outlet Online or in the U.S. when I go home. It’s a big part of what I find addictive about living and working in this part of the world. You feel like you’re watching the future unfold.

dashixiong · 01-29-2013, 12:33 PM

Organizers said Coach Outlet Online was opportune because the battle’s 150-year anniversary is in December, and Fredericksburg Coach Factory Outlet has been preparing to mark the sesquicentennial. in the new agreement is that Coach Outlet Online revolutionary councils from 14 Syrian provinces now each have a representative, though not all live Coach Online Outlet in Syria. The hope is that will bind the coalition to those inside the country. Perhaps Coach Bags Outlet the most important body the new group is expected to form is a Revolutionary Military Council Coach Factory Online to oversee the splintered fighting organizations and to funnel both lethal and nonlethal Coach Factory Outlet military aid to the rebels. It should unite units of the Free Syrian Army, various militias Coach Outlet Store Online and brigades in each city and large groups of defectors. Before the ink was even dry on the Coach Outlet Store final draft, negotiators hoped that it would bring them the antiaircraft missiles they crave to Coach Factory Stores take on the Syrian Air Force. The United States and Britain have offered only Coach Handbags Outlet nonmilitary aid to the uprising. A similar attempt by the Syrian National Council to Coach Factory Store supervise the military never jelled. Organizers said funding was too haphazard. Eventually foreign Coach Factory Online governments like Qatar and Saudi Arabia, which are financing and arming the rebels, found Coach Factory Online their own favorite factions to deal with. Foreign leaders notably including Secretary of State Coach Outlet Hillary Rodham Clinton urged this unification largely so they could coordinate their Coach Factory Outlet efforts and aid through a group of technocrats. Once it receives international recognition, the Coach Outlet Store Online coalition is supposed to establish a temporary Coach Outlet Online military never jelled.