MySQL Performance: MySQL => Using SQL_CALC_FOUND

Members

Help

Home

Home » Performance » MySQL » Using SQL_CALC_FOUND_ROWS

Show: Today's Messages :: Show Polls :: Message Navigator

Using SQL_CALC_FOUND_ROWS [message #18]

Sat, 12 August 2006 07:25

elektronaut
Messages: 9
Registered: August 2006
Location: Switzerland - Zürich - W...

Junior Member

I found this very nice feature SQL_CALC_FOUND_ROWS to use if you want to get an idea of how many records were found, even though you're using limit. I was delighted to find this. Before I was using a count query which was always very annoying to build and it went against all my instincts;)

Is there a catch? Any reason why not to use this feature. It does slow down the query some, but i guess that's to be expected.

Thanks for any comments on this.

Minds are like parachutes - they work best when open.

Report message to a moderator

Re: Using SQL_CALC_FOUND_ROWS [message #25 is a reply to message #18 ]

Sun, 13 August 2006 09:34

Peter
Messages: 405
Registered: August 2006

Senior Member
Super Guru

Yes, There is the catch Smile

SQL_CALC_FOUND_ROWS may be only marginally faster than select count(*) + select with limit, sometimes it could even be slower if EXPLAIN for count(*) is different than one for data retrieval.

If SQL_CALC_FOUND_ROWS is used what happens is - MySQL Simply stops sending rows back to the client after LIMIT clause is satisfied - they still however need to be internally retrieved to be counted.

So in many cases it makes sense to use summary table with precomputed counters instead of count(*) or SQL_CALC_FOUND_rows

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/

Report message to a moderator

Re: Using SQL_CALC_FOUND_ROWS [message #33 is a reply to message #25 ]

Sun, 13 August 2006 12:26

elektronaut
Messages: 9
Registered: August 2006
Location: Switzerland - Zürich - W...

Junior Member

uhh...this is not a very nice discovery;)

First: this means that my indizes aren't used for sorting (see other question)

Second: Previously i always used a count query which was the same as the original without any sorting...which means, it runs a lot faster!

Pity...

Minds are like parachutes - they work best when open.

Report message to a moderator

Re: Using SQL_CALC_FOUND_ROWS [message #35 is a reply to message #33 ]

Sun, 13 August 2006 18:43

Peter
Messages: 405
Registered: August 2006

Senior Member
Super Guru

Well.... In this case you can continue using count(*) + separate query.

Just use what works well Smile

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/

Report message to a moderator

Re: Using SQL_CALC_FOUND_ROWS [message #36 is a reply to message #35 ]

Mon, 14 August 2006 02:46

elektronaut
Messages: 9
Registered: August 2006
Location: Switzerland - Zürich - W...

Junior Member

yeah i guess so. I just liked the SQL_CALC_FOUND_ROWS way because it fit nicely and did not require any special treatment. Trouble also is, that I generate those queries based on some configuration, which means that if i want to optimize every variant, i'll have to check them all and decide what to use and such... Wink

It probably depends on wether I am sorting the data or not...and if there are any joins involved...

Minds are like parachutes - they work best when open.

Report message to a moderator

Re: Using SQL_CALC_FOUND_ROWS [message #37 is a reply to message #36 ]

Mon, 14 August 2006 03:38

Peter
Messages: 405
Registered: August 2006

Senior Member
Super Guru

Well... That is life. Simple solution is not always fastest one.

For example I know users who actually have complex query generation rules for this sake. In some cases even run EXPLAIN for candidate queries to see which will have best execution plan.

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/

Report message to a moderator

Previous Topic:	Using Indexes to increase sort performance
Next Topic:	1-n distinct/group by optimization

Goto Forum:

-=] Back to Top [=-

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [

]

Current Time: Fri Jul 10 01:42:11 EDT 2009

Total time taken to generate the page: 0.04904 seconds

.:: Contact :: Home ::.

MySQL is a trademark of Sun Microsystems.
InnoDB is a trademark of Oracle Corp.

Percona Performance Forums are a service of Percona, Inc.
Not affiliated with Sun Microsystems or Oracle Corp.