Wrox Programmer Forums
Go Back   Wrox Programmer Forums > PHP/MySQL > PHP Databases
| Search | Today's Posts | Mark Forums Read
PHP Databases Using PHP in conjunction with databases. PHP questions not specific to databases should be directed to one of the other PHP forums.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the PHP Databases section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old March 23rd, 2007, 04:03 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Oklahoma City, Oklahoma, USA.
Posts: 249
Thanks: 0
Thanked 0 Times in 0 Posts
Default SQL_CALC_FOUND_ROWS

I was curious, which is the faster and best method when paginating data and why, using the SQL_CALC_FOUND_ROWS in your select statement then later calling the SELECT FOUND_ROWS() verses using a COUNT() to get total records.

Thank You
mike
__________________
Peace
Mike
http://www.eclecticpixel.com
  #2 (permalink)  
Old March 23rd, 2007, 09:03 PM
Friend of Wrox
 
Join Date: Jul 2003
Location: South San Francisco, CA, USA.
Posts: 128
Thanks: 0
Thanked 1 Time in 1 Post
Default

harpua,
I am not sure about the functions you are asking.
My suggestion is to try doing it by time trial,
Let say, have a Time difference query or function to
know in actual datas. Like an onclick button in PHP
will show you the time difference from one to the other.
An example is this with series of sqls to execute.
GROUP 1... COUNT() ----> GET RESULT
1) FUNCTION BY COUNT:
SELECT COUNT(*), NOW() AS TimeBefCount FROM tablename;
exec....
assign to a variable cn1 = countresult
                     tm1 = timebefcount

2) Add another sql INSERT
exc...

3) REDO NO. 1

4) CALCULATE THE DIFF BETWEEN RESULT OF 3 AND 1.

GROUP 2... SQL_CALC_FOUND_ROWS ----> get result
ETC...

hope this helps.
john





  #3 (permalink)  
Old November 1st, 2007, 11:33 PM
Registered User
 
Join Date: Nov 2007
Location: , , United Kingdom.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

When all columns in the WHERE clause are fully indexed then the fastest method is to run 2 selects:

SELECT * FROM table WHERE col=x LIMIT 20
SELECT COUNT(*) FROM table WHERE col=x

This allows the Count to be found from just the index

SQL_CALC_FOUND_ROWS on the other hand uses filesort/temporary tables and not the index and consequently is slower

This is a known bug/feature request http://bugs.mysql.com/bug.php?id=18454

On the other hand, experience/benchmarking has shown me that the situation can be reversed when columns in the WHERE clause are not fully indexed and COUNT(*) is forced into a table scan.

By the way, to benchmark your queries you use EXPLAIN
http://dev.mysql.com/doc/refman/5.0/en/explain.html







Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.