… > 2938575 ORDER BY user_id) t WHERE t.parent_id=0 AND t.status = 1 LIMIT 1 Finally, we can use index hints. Index hints are exactly… I recommend against using index hints for one very simple reason: stuff happens. Just a few possibilities: A new version of MySQL is released that…
Post: Using index for ORDER BY vs restricting number of rows.
February 16, 2007 By 8 Comments
…problem with MySQL Optimizer I frequently run into is making poor decision when it comes to choosing between using index for ORDER BY or using index for restriction. …badly selective using index scan is much better idea. Until MySQL is able to handle this you will have to use force index hint. The …
Post: Full table scan vs full index scan performance
November 23, 2012 By 15 Comments
…use the index? mysql> EXPLAIN SELECT * FROM employees FORCE INDEX(idx_first) ORDER BY first_name\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: employees type: index…INDEX hint to ensure optimal response time. Now let’s modify the query by…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 31 Comments
…order. For example when you query AGE=18 with single column BTREE index MySQL will dive into the index…hint in MySQL which would allow forcing using index merge when MySQL does not think it should be used. I hope hint…
Post: Knowing what pt-online-schema-change will do
November 1, 2012 By 1 Comment
…INDEX(`guest_language`) WHERE ((`guest_language` > ?) OR (`guest_language` = ? AND `guest_country` > ?) OR (`guest_language` = ? AND `guest_country` = ? AND `score` >= ?)) ORDER BY…
Comment: What does Using filesort mean in MySQL?
March 6, 2009 By 27 Comments
… id, then the index you’re creating won’t be used for the optimization. Here’s an example with another schema: mysql> create table… Extra: Using filesort 1 row in set (0.00 sec) You can hint the optimizer: mysql> explain select * from t1 force index (id) order by id\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 type: index…
Post: MySQL Session variables and Hints
December 28, 2006 By 2 Comments
MySQL has two ways to find tune execution of particular query. First is MySQL Hints, such as SQL_BIG_RESULT, STRAIGHT_JOIN, FORCE INDEX… in production hints are used much more frequently than setting session variables for given …_SIZE=50000000 NAME FROM LARGE_TABLE ORDER BY NAME DESC LIMIT 10 This …
Post: MySQL Upgrade Webinar Questions Followup
June 28, 2012 By 7 Comments
…MySQL 5.0 to MySQL 5.5 should be rather safe from data standpoint. There are still possibilities for sorting order… recommend using Percona Server with enabled expand_fast_index_creation feature…hints to get to old execution plans. In many cases though you can get to former execution plan either by…
Post: When the subselect runs faster
March 18, 2010 By 9 Comments
… is often a lot faster to retrieve rows in order checking WHERE clause for them until required number of rows were returned… in index order. We can just use FORCE INDEX hint to override MySQL index choice: mysql> explain select * from table FORCE INDEX(PRIMARY) where (col1=’A'||col1=’B') order by id…
Post: Analyzing air traffic performance with InfoBright and MonetDB
October 2, 2009 By 50 Comments
…BY carrier ORDER BY 2 DESC [ "WN", 296293 ] [ "AA", 176203 ] … With 0.27s for MonetDB and 0.99sec for…indexes like InfoBright, but results are impressive. On drawbacks – the command line is weak ( I had to use…
