… nibble*/ SELECT `guest_language`, `guest_country`, `score` FROM `test`.`t` FORCE INDEX(`guest_language`) WHERE ((`guest_language` > ?) OR (`guest_language` = ? AND `guest… about indexes.) … Notice in each SQL statement the clause: FORCE INDEX(`guest_language`) The tool has chosen to use the guest_language index instead…
Post: Getting around optimizer limitations with an IN() list
January 9, 2010 By 9 Comments
… some execution plans on dummy data: EXPLAIN SELECT * FROM coordinates FORCE INDEX (x_y_col_a) WHERE x BETWEEN 30 and 40… row in set (0.00 sec) EXPLAIN SELECT * FROM coordinates FORCE INDEX (x_y_col_a) WHERE x BETWEEN 30 and 40… row in set (0.01 sec) SELECT count(*) FROM coordinates FORCE INDEX (x_y_col_a) WHERE x BETWEEN 30 AND 40…
Post: Getting MySQL to use full key length
January 31, 2007 By 5 Comments
… only part of the index when full index can be used or using shorter index while there is longer index available. The last item… side effects FORCE INDEX actually forces index to be used to largest extent possible: mysql> explain SELECT thread_id FROM nn2_msg132.msg132 force index(group… (just do not have example today) is using different index, for example, having indexes (A) and (A,B) for query A=Const…
Post: When the subselect runs faster
March 18, 2010 By 9 Comments
… scanning table in index order. We can just use FORCE INDEX hint to override MySQL index choice: mysql> explain select * from table FORCE INDEX(PRIMARY) where… | 1 | SIMPLE | table | index | NULL | PRIMARY | 4 | NULL | 549117 | Using where | +—-+————-+——-+——-+—————+———+———+——+——–+————-+ mysql> select * from table FORCE INDEX(PRIMARY) where (col1=’A'||col1=’B…
Post: Do you always need index on WHERE column ?
August 28, 2007 By 17 Comments
…’s check execution time with and without index mysql> select count(name) from testr force key (has_something) where has_something=0… times slower. and this time mysql is going to use index in execution plan: mysql> explain select count(name) from testr… without index is faster. And finally for case with 20% rows with has_someting=0 mysql> select count(name) from testr force…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 100 Comments
… long as it is indexed) Force index if needed In some cases MySQL Optimizer may prefer to use different index, which has better selectivity… extending your indexes so MySQL Optimizer does not have to chose between better sort or better lookup or use FORCE INDEX to force it to use appropriate index. One more note about ORDER BY … LIMIT…
Comment: 3 ways MySQL uses indexes
July 20, 2010 By 28 Comments
that you see more of it, I created 2 new indexes (on production data, not testing or random). (sizes are different… in app FROM tree FORCE INDEX (ty_fr) : key ty_fr key_len 7 rows 11410 FROM tree FORCE INDEX (ty_fr_to) : key ty_fr_to key_len 10 rows 8492 FROM tree FORCE INDEX (ty_fr_to…
Comment: ORDER BY ... LIMIT Performance Optimization
November 17, 2009 By 100 Comments
…| 22771 | NULL | NULL | | BTREE | | +——-+————+——————+————–+————-+———–+————-+———-+——–+——+————+———+ mysql> explain SELECT posts.* FROM posts force index (idx_post_date) INNER JOIN follow ON follow.followuserid = posts…
Comment: Using delayed JOIN to optimize count(*) and LIMIT queries
April 8, 2007 By 22 Comments
…, ‘%m-%d-%y’ ) As EDate, EHC.Hits FROM Entries E FORCE INDEX (DPrivate) LEFT JOIN EntryHitCounters EHC ON (EHC.EntryID=E.EntryID…-%y’) as EDate, EHC.Hits from (select EntryID from Entries FORCE INDEX (DPrivate) where DiaryID=11693 and Private=0 Order By Date… (EntryID) LEFT JOIN EntryHitCounters EHC USING (EntryID) I wish the force index wasn’t needed.
Post: When is MIN(DATE) != MIN(DATE) ?
September 4, 2012 By 5 Comments
… UNSIGNED NOT NULL, update_time DATETIME NOT NULL, …. INDEX `uid` (uid, update_time), INDEX `bar` (some_other_columns) …. ) ENGINE=InnoDB; When he… try to force MySQL to use a different index. Imagine our surprise when we tried a FORCE INDEX on (bar) or an IGNORE INDEX(uid…
