…… Optimizer decides to use index merge in this case which is probably the worse decision it could do. Indeed the query takes 360ms Note… index. The query takes 290ms which is exactly what we’ve seen before. What if we leave MySQL no choice but only to use combined index: mysql… know of the hint in MySQL which would allow forcing using index merge when MySQL does not think it should be used. I hope hint would…
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
…the queries to use ANSI JOIN syntax. No other changes to the queries were made. Test Environment The MySQL versions used…table by primary key. This forces the storage engine to touch the same page numerous …the other secondary indexes have been pushed out of the buffer pool. mysql> select * from …
Post: Troubleshooting MySQL Upgrade Performance Regressions
February 15, 2012 By Leave a Comment
…To spot what exactly is causing the problem it is good to use pt-query-digest to…INDEXES FROM for tables involved and check cardinality) Different stats can often cause different plans. Run ANALYZE TABLE on both MySQL Versions to…
Post: MySQL Slow query log in the table
May 31, 2007 By 1 Comment
…MySQL slow query log logged in mysql.slow_log table instead of the file as you had in previous versions. We rarely would use…queries is not fast. The good thing however you’re allowed to change table to other storage engine and add extra indexes…
Post: A micro-benchmark of stored routines in MySQL
August 16, 2009 By 7 Comments
… mis-optimized to force a bad execution plan. You should never use IN() subqueries the way I do, at least not in MySQL 5… similar to the first table shown above, but the further two rows are not shown. The query can’t be optimized to use indexes, and the stored function is opaque to the optimizer. This is why…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 100 Comments
…to use index – in this case index range scan will be started and query…to use index to do ORDER BY with JOIN MySQL still will not be able to use it as Optimizer is not smart enough yet to…
Post: Full table scan vs full index scan performance
November 23, 2012 By 15 Comments
…index as a relevant choice (possible_keys: NULL). What do we get if we force the optimizer to use the index? mysql> EXPLAIN SELECT * FROM employees FORCE INDEX… 300584 Extra: Using index The optimizer chooses the full index scan. It is a good choice because the index now covers the query, which means…
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 10 Comments
… is a query which used both a and id key part from original index it will quite likely be unable to use new index to full extent… using (a) index you can get result scanning only 1 row guaranteed. To really get things going you will need to use FORCE INDEX(a) to force MySQL optimizer using right…
Post: Why Index could refuse to work ?
September 8, 2006 By 15 Comments
…using “”. You may ask why MySQL can’t use index in this case, simply by converting number to the string and performing index lookup ? This can’t be done as it would result in wrong result for some queries…
Post: Multiple column index vs multiple indexes
August 22, 2008 By 13 Comments
…to force index merge as you can do with index accesses in general. Note2 Q2/Q3 can’t use Index…use index well. You’re right MySQL can’t and MySQL does not – Full table scan is performed and in this case turns to…
