… things like whether Query cache was used or not, if Filesort was used, whether tmp table was created in…overview section. So say if you were using the vanilla MySQL server, you would see an entry like…*/ SELECT option_name, option_value FROM wp_options WHERE autoload = ‘yes’\G This is the actual part…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
Post: Possible optimization for sort_merge and UNION ORDER BY LIMIT
September 18, 2007 By 10 Comments
… Extra: Using sort_union(c1,c2); Using where; Using filesort 1 row in set (0.00 sec) As you can see MySQL 5.1.21 uses sort… key: NULL key_len: NULL ref: NULL rows: NULL Extra: Using filesort 3 rows in set (0.00 sec) The query looks… key: NULL key_len: NULL ref: NULL rows: NULL Extra: Using filesort 3 rows in set (0.00 sec) As you can…
Post: Updated msl (microslow) patch, installation walk-through!
April 20, 2008 By 17 Comments
…. Please do not use the below example as it may not be suitable for your environment. (garfield:~/work/mysql-5.0.51a… to have the patched MySQL installed in the system. Locate where the original mysqld binary is with: (garfield:~/work/mysql-5.0.51a… get only those which use on-disk temporary storage for intermediate results “tmp_table_on_disk,filesort_on_disk†would be…
Post: Is it query which needs to be optimized ?
March 6, 2007 By 9 Comments
…use different solutions, such as summary table. With MySQL 5.0 it is also easy to implement one by use…to make them efficient – make sure all where clauses are using indexes, try to have queries index …generation when some query variants fall back to use filesort, and it does not take many of such…
Comment: ORDER BY ... LIMIT Performance Optimization
September 20, 2006 By 100 Comments
…’m using MySQL 5 and am having some fun adapting to their new optimization engine. I have a table that I use a…_title_stripped match_url_title_stripped 0 1 Using where; Using filesort — Versus — SELECT * FROM sites WHERE MATCH(title, url, stripped) AGAINST(‘test’ IN BOOLEAN…
Post: Profiling MySQL stored routines
January 19, 2009 By 4 Comments
… # Filesort: No Filesort_on_disk: No Merge_passes: 0 # No InnoDB statistics available for this query SELECT whatstheweatherlike(5); Not overly useful for… where Jeremy’s SHOW PROFILE patch – which is included in all Community tree based Percona MySQL builds since it is part of MySQL…
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 10 Comments
…a | 4 | const | 1 | Using where; Using index; Using filesort | …Using where | +—-+————-+———-+——-+—————+———+———+——+——+————-+ 1 row in set (0.00 sec) MySQL Optimizer considers using…
Post: To SQL_CALC_FOUND_ROWS or not to SQL_CALC_FOUND_ROWS?
August 28, 2007 By 57 Comments
…Using where | +—-+————-+————+——+—————+——+———+——-+——-+————-+ 1 row in set (0.00 sec) mysql> explain SELECT SQL_NO_CACHE count(*) FROM count_test WHERE…
Comment: Multiple column index vs multiple indexes
August 26, 2008 By 13 Comments
What about the impact of multiple column index on ORDER BY speed ? e.g. : SELECT * FROM a WHERE b=1 AND c=2 ORDER BY d Here, an index on (b,c,d) will avoid MySQL using filesorting. If three indexes are put on b, c and d, will MySQL be able to use index merge on d to avoid filesorting ?
Comment: Multi Column indexes vs Index Merge
May 10, 2012 By 31 Comments
…why MySQL uses index merge after a couple of mins when previously it was using…| const | PRIMARY,namespace_ix | PRIMARY | 4 | const | 1 | Using filesort | | 1 | SIMPLE | LinksTbl2 | index_merge | … | 4,1 | NULL | 294 | Using intersect(prjid_ix,item_desc); Using where | | 1 | SIMPLE | PersonTbl2 | eq_…
