…); Using where; Using index; Using filesort At first glance, this might not look too bad. MySQL is using three different indexes to… t.status = 1 LIMIT 1 Finally, we can use index hints. Index hints are exactly what they sound like – “help” for the… against using index hints for one very simple reason: stuff happens. Just a few possibilities: A new version of MySQL is released…
Post: Using index for ORDER BY vs restricting number of rows.
February 16, 2007 By 8 Comments
…,const rows: 296338 Extra: Using where; Using filesort 1 row in set (0.00 sec) mysql> explain select * from goods force index… (0.00 sec) As you can see if given no hint MySQL will prefer to use index on (cat_id,seller_id… million of rows to analyze but we got rid of filesort so MySQL can stop as soon as 10 rows are sent…
Post: Updated msl (microslow) patch, installation walk-through!
April 20, 2008 By 17 Comments
… hints about installing MySQL on your platform. Also have a look at the files in the Docs directory. Thank you for choosing MySQL…, full_scan, full_join, tmp_table, tmp_table_on_disk, filesort, filesort_on_disk] It allows you to filter queries logged by… temporary storage for intermediate results “tmp_table_on_disk,filesort_on_disk†would be a proper flags set. To clear…
Post: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization
September 17, 2007 By 14 Comments
… is – it is using filesort as group by execution method, not temporary table as ordinary GROUP BY: mysql> explain select grp, count… Extra: Using where; Using temporary; Using filesort 1 row in set (0.01 sec) mysql> explain select grp, count(*) cnt from dt… filesort 1 row in set (0.00 sec) As I forced FileSort execution method for GROUP BY by using SQL_BIG_RESULT hint…
Comment: What does Using filesort mean in MySQL?
March 6, 2009 By 27 Comments
… affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> explain select * from t1 order by id\G *************************** 1. row *************************** id… ref: NULL rows: 4 Extra: Using filesort 1 row in set (0.00 sec) mysql> alter table t1 add index(id); Query…: NULL rows: 4 Extra: Using filesort 1 row in set (0.00 sec) You can hint the optimizer: mysql> explain select * from t1…
Post: Full table scan vs full index scan performance
November 23, 2012 By 15 Comments
…s see how the optimizer will execute it: mysql> EXPLAIN SELECT * FROM employees ORDER BY first_name\G …len: NULL ref: NULL rows: 300363 Extra: Using filesort Surprising? The optimizer preferred a full table scan… memory, we should add the FORCE INDEX hint to ensure optimal response time. Now let…
Post: When the subselect runs faster
March 18, 2010 By 9 Comments
…| 549252 | Using where; Using filesort | +—-+————-+——-+——-+—————+——+———+——+——–+—————————–+ This query …MySQL check WHERE clause while scanning table in index order. We can just use FORCE INDEX hint to override MySQL index choice: mysql…
