… plan. (I know it doesn’t though.) Why don’t you try writing a SQL query …produce an infinite variety of queries. The query optimizer analyzes queries for common patterns the MySQL designers know can be … It was more efficient in this case to force MySQL to scan the `title` table first, grouping …
Post: Troubleshooting MySQL Upgrade Performance Regressions
February 15, 2012 By Leave a Comment
…differently between MySQL Server versions you should: Check Query Plan Run EXPLAIN to see if plans for the query are the same. Changing Query Execution plans … be forced to change your application by changing how query is written and provide the hints. Check Query Execution If query execution plan …
Post: Guidance for MySQL Optimizer Developers
September 20, 2009 By 4 Comments
… to old optimizer behavior with new MySQL Version. Make Cost Model Adjustable MySQL Optimizer looks at query plan in terms of disk IOs/Seeks… MySQL has proper ways to execute the query. It may not always pick them right but at least it allows to get query… as simply hints. Any way MySQL could possibly execute query should be possible to force with hints. In MySQL 4.0 this was the…
Post: The MySQL optimizer, the OS cache, and sequential versus random I/O
April 28, 2008 By 17 Comments
… cost for a query, then choose the query plan that has the lowest cost. The unit of cost for the MySQL query optimizer is a… on every column in the WHERE clause. Here’s the query plan initially. *************************** 1. row *************************** table: dim1 type: range key_len: 195… the two dimension tables. MySQL doesn’t want to choose this join order, so we’ll force it with STRAIGHT_JOIN: explain…
Post: Announcing Percona Server for MySQL version 5.1.67-14.4
March 8, 2013 By Leave a Comment
… command line option innodb_changed_pages_limit. MySQL option handling would then shadow the former …changed page tracking wasn’t compatible with innodb_force_recovery=6. When starting the server log … and the query plan information were being logged even if they weren’t enabled for the slow query log…
Post: Announcing Percona Server for MySQL version 5.5.29-30.0
February 26, 2013 By Leave a Comment
… command line option innodb_changed_pages_limit. MySQL option handling would then shadow the former …changed page tracking wasn’t compatible with innodb_force_recovery=6. When starting the server log … and the query plan information were being logged even if they weren’t enabled for the slow query log…
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 10 Comments
… not hurt any other queries a lot, right ? mysql> alter table idxitest drop key a,add key(a,b); Query OK, 0 rows… things going you will need to use FORCE INDEX(a) to force MySQL optimizer using right plan. These results mean you should be very… checker when it comes to redundant indexes. If you have query plans depending on Innodb ordering of data by primary key inside…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
…that fast on big joins; the query optimizer can sometimes pick bad plans when you have a lot of …of-the-box than MySQL is, especially for more complex queries. Percona is not tied to MySQL, although we’re … But beware of falling into the trap of brute-forcing a solution that really needs to be solved …
Post: Getting MySQL to use full key length
January 31, 2007 By 5 Comments
… the same data is traversed anyway. So how do you force MySQL optimizer to use full length in this case ? ANALYZE TABLE…). MySQL could look at the stats and adjust them appropriately – the plan for second query is accessing subset of rows from the first plan… indexes (A) and (A,B) for query A=Const and B>Const we can see MySQL selecting (A) index instead of (A…
Post: ANALYZE: MyISAM vs Innodb
September 3, 2008 By 9 Comments
… good plans. If query plans are good or bad independently of it being run there is need to bother – for bad plans use FORCE INDEX or change the query and report MySQL Optimizer Bug 
But now lets see… may change stats dramatically and affect some query plans. You also may be getting different plans on different slaves with same data. Another…
