…problem with MySQL Optimizer I frequently run into is making poor decision when it comes to choosing between using index for ORDER BY or using index for restriction. …badly selective 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
…order. For example when you query AGE=18 with single column BTREE index MySQL will dive into the index…hint in MySQL which would allow forcing using index merge when MySQL does not think it should be used. I hope hint…
Comment: What does Using filesort mean in MySQL?
March 6, 2009 By 27 Comments
… id, then the index you’re creating won’t be used for the optimization. Here’s an example with another schema: mysql> create table… Extra: Using filesort 1 row in set (0.00 sec) You can hint the optimizer: mysql> explain select * from t1 force index (id) order by id\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 type: index…
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… in production hints are used much more frequently than setting session variables for given …_SIZE=50000000 NAME FROM LARGE_TABLE ORDER BY NAME DESC LIMIT 10 This …
Post: When the subselect runs faster
March 18, 2010 By 9 Comments
… is often a lot faster to retrieve rows in order checking WHERE clause for them until required number of rows were returned… in index order. We can just use FORCE INDEX hint to override MySQL index choice: mysql> explain select * from table FORCE INDEX(PRIMARY) where (col1=’A'||col1=’B') order by id…
Post: Analyzing air traffic performance with InfoBright and MonetDB
October 2, 2009 By 50 Comments
…BY carrier ORDER BY 2 DESC [ "WN", 296293 ] [ "AA", 176203 ] … With 0.27s for MonetDB and 0.99sec for…indexes like InfoBright, but results are impressive. On drawbacks – the command line is weak ( I had to use…
Comment: How to find wrong indexing with glance view
March 8, 2010 By 27 Comments
…indexes and tables to choose from for moderately complex queries and selects an extraordinarily inefficient plan resulting in horrendous performance by using…MySQL will create the following indexes: unique index on order_id index on item_type_id index on locale_id index on currency_id index…
