… b in where 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… 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 in case…
Comment: To SQL_CALC_FOUND_ROWS or not to SQL_CALC_FOUND_ROWS?
August 29, 2007 By 57 Comments
… in “EXPLAIN SELECT SQL_CALC_FOUND_ROWS * FROM count_test WHERE b = 999 ORDER BY c LIMIT 5;”, mysql uses index to determine number of rows to… takes some time (because columns are not included in index). I think use SQL_CALC_FOUND_ROWS in queries that use complex where (for example search…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
… InnoDB 442 mysql tables in use 1, locked 0 MySQL thread id 8079, query id 728899 localhost root Sending data select sql_calc_found_rows * from b… InnoDB 166 mysql tables in use 1, locked 0 MySQL thread id 8078, query id 728898 localhost root Sending data select sql_calc_found_rows * from b…
Comment: To SQL_CALC_FOUND_ROWS or not to SQL_CALC_FOUND_ROWS?
September 11, 2009 By 57 Comments
…. in this case you have to use mysql_num_rows or SQL_CALC_FOUND_ROWS , mysql_num_rows runs very slow. we have only one option it is SQL_CALC_FOUND_ROWS. if…
Comment: To SQL_CALC_FOUND_ROWS or not to SQL_CALC_FOUND_ROWS?
August 29, 2007 By 57 Comments
… version of mysql server did you use? Does this apply to all current versions or is the SQL_CALC_FOUND_ROWS option more optimized in mysql 5 ? Depending on the availability of indexes, mysql should be able to optimize a SQL_CALC_FOUND_ROWS query in such…
Comment: Four ways to optimize paginated displays
September 24, 2008 By 27 Comments
… how many pages the results set will span?: { 1. SELECT SQL_CALC_FOUND_ROWS id FROM table ORDER BY foo LIMIT offset, size 2… WHERE id IN (collectedIds) 4. re-arrange ids 5.SELECT FOUND_ROWS(); 1 usually requires as little time as possible for mySQL will usually… little time as it could take; mySQL can fetch the record information for the rows required as efficiently as it could ( i…
Post: Is it query which needs to be optimized ?
March 6, 2007 By 9 Comments
… of questions at MySQL Performance Forum as well as from our customers regarding query optimization… which had one thing in common – It… for large number of rows you’ve simply got to use different solutions, such as summary table. With MySQL 5.0 it… certain number of rows in result set, with results set of 100.000+ it becomes pretty inefficient. There is surely SQL_CALC_FOUND_ROWS but it…
Post: Are you designing IO bound or CPU bound application ?
December 20, 2006 By 10 Comments
… it is extremely important for MySQL Performance Optimization. In fact I probably have to touch it in every second MySQL Consulting work or even… the query which analyzes 10000 rows it often would take fraction of the second with fully in memory workload, however if you… select count(*) from messages where user_id=134 or use SQL_CALC_FOUND_ROWS flag for your main select query. If you’re having…
Comment: To SQL_CALC_FOUND_ROWS or not to SQL_CALC_FOUND_ROWS?
March 8, 2010 By 57 Comments
Agree with Nadeem If you are using SELECT SQL_CALC_FOUND_ROWS, MySQL must calculate how many rows are in the full result set. However, this is faster than running the query again without LIMIT, because the result set need not be sent to the client.
Post: Bug fix of InnoDB scalability problem
November 14, 2006 By 13 Comments
… now I have it in my hands. Let me refresh results with MySQL 5.0.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 in 4 concurrent…
