…clause: mysql> SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS * FROM count_test WHERE b = 555 ORDER BY c LIMIT 5; Results with SQL_CALC_FOUND_ROWS are following: for… 7 seconds for SQL_CALC_FOUND_ROWS. 7+7 seconds in case when two queries used. Filesort: 1.8 seconds for SQL_CALC_FOUND_ROWS. 1.8+0.05 seconds…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
… in use 1, locked 0 MySQL thread id 8079, query id 728899 localhost root Sending data select sql_calc_found_rows * from b limit 5 Trx… in use 1, locked 0 MySQL thread id 8078, query id 728898 localhost root Sending data select sql_calc_found_rows * from b limit 5 Trx… in use 1, locked 0 MySQL thread id 8077, query id 728897 localhost root Sending data select sql_calc_found_rows * from b limit 5 Trx…
Comment: Four ways to optimize paginated displays
September 24, 2008 By 27 Comments
… pages the results set will span?: { 1. SELECT SQL_CALC_FOUND_ROWS id FROM table ORDER BY foo LIMIT offset, size 2. Collect ids(i.e… WHERE id IN (collectedIds) 4. re-arrange ids 5.SELECT FOUND_ROWS(); 1 usually requires as little time as possible for mySQL will… LIMIT offset, size + 1 2. Collect ids(i.e columns that comprise the primary key value), except the last one if total rows…
Comment: To SQL_CALC_FOUND_ROWS or not to SQL_CALC_FOUND_ROWS?
August 23, 2008 By 57 Comments
I have to say that COUNT(*) is faster so far. Query with LIMIT and SQL_CALC_FOUND_ROWS: 0.80 – 1.00 seconds Query without SQL_CALC_FOUND_ROWS: 0.06 seconds Added COUNT(*) query: 0.09-0.12 seconds
Comment: Backport of micro-time patch to mysql 4.1
October 9, 2006 By 9 Comments
…: 7 Lock_time: 0 Rows_sent: 1 Rows_examined: 227073 use mindmix; SELECT SQL_CALC_FOUND_ROWS * FROM `topics` WHERE `forum_id` !=1 LIMIT 1; Do you have another ideas…
Post: Bug fix of InnoDB scalability problem
November 14, 2006 By 13 Comments
….27 (without bugfix): select sql_calc_found_rows * from b limit 5; executes for 20 sec (table b contains ~2 mil rows) the same query but… for each thread). The results with 5.0.30-bk: 1 thread: 19 sec 4 threads: 28 sec 29 sec 39… you see there is a thread starvation – last thread executes 1.4 times slower. So I’d want InnoDB team continue…
Comment: Join performance of MyISAM and Innodb
April 3, 2010 By 6 Comments
…SQL_CALC_FOUND_ROWS p.*, FLOOR(p.prodratingtotal/p.prodnumratings) AS prodavgrating, …ps.prodsearchkeywords) AGAINST (‘gold’)) ORDER BY score DESC LIMIT 20 EXECUTION TIME: 2.5000+ seconds TABLES DATA: …vis PRIMARY 4 shoppingcart_5521.ps.productid 1 Using where 1 SIMPLE pi ref i_product_images_…
Comment: JOIN Performance & Charsets
April 3, 2010 By 2 Comments
…level. Details are as under QUERY: SELECT SQL_CALC_FOUND_ROWS p.*, FLOOR(p.prodratingtotal/p.prodnumratings) AS …prodsearchkeywords) AGAINST (‘gold’)) ORDER BY score DESC LIMIT 20 EXECUTION TIME: 2.5000+ seconds TABLES …PRIMARY 4 shoppingcart_5521.ps.productid 1 Using where 1 SIMPLE pi ref i_product_…
Comment: To SQL_CALC_FOUND_ROWS or not to SQL_CALC_FOUND_ROWS?
December 21, 2012 By 57 Comments
…specific situation, using MyIsam engine and relatively small limit with a simple query on a big DB… (big) news publishing site with MySql 5.1 and InnoDB engine and real life queries (…opposite results… which makes sense sort of, since SQL_CALC_FOUND_ROWS was added to optimize queries in the first…
