… it without side load. Setting up 2 MySQL Servers side by side (for example with MySQL Sandbox) can especially be helpful. Once you have spotted the query which performs differently between MySQL Server versions you should: Check Query Plan Run EXPLAIN to… manually to the old one by using hints such as STRAIGHT_JOIN, FORCE INDEX, BIG_RESULT/SMALL_RESULT. Check whatever stats are the…
Post: The MySQL optimizer, the OS cache, and sequential versus random I/O
April 28, 2008 By 17 Comments
… two dimension tables. MySQL doesn’t want to choose this join order, so we’ll force it with STRAIGHT_JOIN: explain select STRAIGHT_JOIN …. +——-+———–+———–+———————————+ | table | type | rows… information. If the MySQL optimizer were right and each of these had to go to disk, our STRAIGHT_JOIN plan would take more…
Post: Be careful when joining on CONCAT
October 16, 2007 By 10 Comments
…(11) column. No matter what I did – forced it to use key, forced a different join order, it did not want to use…, the number of rows analyzed was really huge: mysql> EXPLAIN -> SELECT -> tb1.* -> FROM tb2 -> STRAIGHT_JOIN tb1 -> WHERE -> ( -> tb1.vid LIKE ‘prefix-%’ AND…… OK, let’s check if that really helps: mysql> EXPLAIN -> SELECT -> tb1.* -> FROM tb2 -> STRAIGHT_JOIN tb1 -> WHERE -> ( -> tb1.vid LIKE ‘prefix-%’ AND…
Post: MySQL Session variables and Hints
December 28, 2006 By 2 Comments
MySQL has two ways to find tune execution of particular query. First is MySQL Hints, such as SQL_BIG_RESULT, STRAIGHT_JOIN, FORCE INDEX etc. You place these directly into the query to change how query is executed for example SELECT STRAIGHT_JOIN * FROM A FORCE…
Comment: How to find wrong indexing with glance view
March 8, 2010 By 27 Comments
… but not so much in 2010. MySQL surprisingly supports declarative integrity constraints. But wait being MySQL there must be something about it… you need to help it out with optimizer hints like straight_join to have it look at tables in the right order… single column indexes generally aren’t a good idea, well MySQL forces this upon you if you actually implement a proper database…
