… | NULL | 959 | Using intersect(i2,i1); Using where | +—-+————-+———+————-+—————-+——-+———+——+——+————————————-+ 1 row in set (0.00 sec) Hm… Optimizer decides to use index merge in this case which is probably the worse decision it could do. Indeed the query takes 360ms Note… hint in MySQL which would allow forcing using index merge when MySQL does not think it should be used. I hope hint would be…
Post: Multiple column index vs multiple indexes
August 22, 2008 By 13 Comments
… to fit in the systems memory. I’ve benchmarked simple queries using where clause which covers multiple columns: Q1 SELECT sum(length… would allow to force index merge as you can do with index accesses in general. Note2 Q2/Q3 can’t use Index Merge however as it is currently implemented so they would use single index range scan. The 2 indexes however benefits…
Post: MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown
March 14, 2013 By 2 Comments
…_note key_len: 4 ref: const rows: 10259274 Extra: Using where; Using index 1 row in set (0.00 sec) I reported this… to make a query faster, which means you need more manual care and tuning now. MySQL is conservative about “Using index” -in most…. There is no workaround, using FORCE-like commands or optimizer_switch flags- we can disable ICP, but not “using index“. So, I wouldn’t…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 100 Comments
… it is important for it to use index – in this case index range scan will be started and query execution stopped as soon as… 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… explain statements and may end up in slow query log as query which does not use indexes, even if it is quite fast: mysql…
Post: Getting MySQL to use full key length
January 31, 2007 By 5 Comments
… 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_id… using different index, for example, having indexes (A) and (A,B) for query A=Const and B>Const we can see MySQL selecting (A) index…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
…Using index | +—-+————-+——-+——-+——————————————–+———+———+——————-+———+———-+———————————————-+ And the results in seconds of time taken to complete the above query…
Post: InnoDB: look after fragmentation
November 5, 2009 By 12 Comments
… | +——————————+——-+ 2 rows in set (0.00 sec) and extended stats: # Query_time: 17.765369 Lock_time: 0.000137 Rows_sent: 1… may use –order-by-primary options to force dump in primary key order. So notes to highlight: InnoDB fragmentation may hurt your query… INDEX creation) so be careful with queries scan many records by secondary key To check if you query affected by fragmentation you can use…
Post: ANALYZE: MyISAM vs Innodb
September 3, 2008 By 9 Comments
… many simple “queries” (using constants for index accesses) will not use index cardinality data at all but will estimate number of rows during query execution. I… run there is need to bother – for bad plans use FORCE INDEX or change the query and report MySQL Optimizer Bug 
But now lets…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 4 Comments
… table. This means many point queries, say for example if table1 yields 1000 rows then 1000 index lookups are performed on table2… query used is: select l_orderkey, sum(l_extendedprice * (1 – l_discount)) as revenue, o_orderdate, o_shippriority from customer, orders, lineitem FORCE INDEX…
Comment: ORDER BY ... LIMIT Performance Optimization
November 17, 2009 By 100 Comments
… mysql> explain SELECT posts.* FROM posts force index (…Using index | +—-+————-+——–+——–+—————+—————+———+——————————-+——+————-+ Is there a way to optimize this query…
