Peter, I think even changing the query to use index/join hints can bring down the time its taking the query to complete, did you try with STRAIGHT_JOIN and/or index hints.
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… this blog post is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6… is, and what is it aimed at. Index Condition Pushdown Traditional B-Tree index lookups have some limitations in cases such as range scans, where index parts after the part on which range condition is applied…
Comment: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 11, 2012 By 2 Comments
@Igor, Yes you are right the original query does not have an index hint, but I have had to make the QEP selected by the optimizer consistent otherwise it would keep flipping. I would post the numbers for the query without the index hint soon.
Post: Using index for ORDER BY vs restricting number of rows.
February 16, 2007 By 7 Comments
… to use index for further restriction and than using file sort, rather than using index for sorting and doing non-index based filtering… sec) As you can see if given no hint MySQL will prefer to use index on (cat_id,seller_id) and sort… using index scan is much better idea. Until MySQL is able to handle this you will have to use force index hint. The…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 30 Comments
… single column indexes (by hinting optimizer to ignore combined index) mysql [localhost] {msandbox} (test) > explain select avg(length(val)) from idxtest ignore index (combined… hint would be added at some point. Finally let me mention the case when Index merge works much better than multiple column indexes…
Post: MySQL Session variables and Hints
December 28, 2006 By 2 Comments
… of particular query. First is MySQL Hints, such as SQL_BIG_RESULT, STRAIGHT_JOIN, FORCE INDEX etc. You place these directly into… is executed for example SELECT STRAIGHT_JOIN * FROM A FORCE INDEX(A) JOIN B The other part is session variable. If…_size=DEFAULT after executing the query. I noticed in production hints are used much more frequently than setting session variables for…
Comment: Why MySQL could be slow with large tables ?
June 9, 2006 By 139 Comments
… not mentioned it in the article but there is IGNORE INDEX() hint to force full table scan. There is no rule of… speaking about ? OPTIMIZE helps for certain problems – ie it sorts indexes themselves and removers row fragmentation (all for MYISAM tables). It…’t make row retrieval which is done by index sequential one. One more hint if you have all your ranges by specific…
Comment: Why InnoDB index cardinality varies strangely
October 8, 2009 By 5 Comments
… with the various indexes on tables. The query would not use the same execution plan for each execution with Index Hints, but when we analyze the table the execution plan is as per the Index hints. I am not…
Post: Multiple column index vs multiple indexes
August 22, 2008 By 13 Comments
… Index Merge technique for low cardinality table but instead pick to do single index scan. I’m not aware of the optimizer hint which would allow to force index merge as you can do with index accesses in general. Note2 Q2/Q3 can’t use Index…
Post: When the subselect runs faster
March 18, 2010 By 9 Comments
… an index on the col1. MySQL estimated the cardinality of that index as 87, though what was of course misleading as index cardinality… 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… | 4 | NULL | 549117 | Using where | +—-+————-+——-+——-+—————+———+———+——+——–+————-+ mysql> select * from table FORCE INDEX(PRIMARY) where (col1=’A'||col1=’B') order by id desc…
