… 1 row in set (0.00 sec) Why on the earth index would not be used you would think…keys” ? Should you try to force it ? mysql> explain select * from article force index (PRIMARY) where article_id=10; +—-+————-+———+——+—————+——+———+——+——-+————-+ | …
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 9 Comments
…FORCE INDEX(a) to force MySQL optimizer using right plan. These results mean you should be very careful applying index…
Post: Multiple column index vs multiple indexes
August 22, 2008 By 13 Comments
… single index scan. I’m not aware of the optimizer hint which would allow to force index merge as you can do with index accesses in general. Note2 Q2/Q3 can’t use Index Merge however… not be very selective. Note3 You may be surprised why 2 column index is faster for Q3 in case of low cardinality…
Comment: Why Index could refuse to work ?
June 15, 2009 By 13 Comments
… reasons why FORCE INDEX is ignored? I have a table with 100 columns which about 25% are integers and most of those have indexes… left join to another table which also has an (btree) index on it, but for some reason it is not being…
Post: Getting around optimizer limitations with an IN() list
January 9, 2010 By 9 Comments
… people commented that some solutions wouldn’t scale. To understand why these sorts of geographic search queries are problematic in MySQL… some execution plans on dummy data: EXPLAIN SELECT * FROM coordinates FORCE INDEX (x_y_col_a) WHERE x BETWEEN 30 and 40… row in set (0.00 sec) EXPLAIN SELECT * FROM coordinates FORCE INDEX (x_y_col_a) WHERE x BETWEEN 30 and 40…
Post: ANALYZE: MyISAM vs Innodb
September 3, 2008 By 9 Comments
… with perfect stats why bother updating them ? Also note many simple “queries” (using constants for index accesses) will not use index cardinality data… run there is need to bother – for bad plans use FORCE INDEX or change the query and report MySQL Optimizer Bug 
But… is enough. Sometimes it is not and you’re thinking why a hell it could be picking this strange plan. Let…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 30 Comments
… indexes on AGE and STATE columns. The better strategy is often to have combined multi-column index on (AGE,STATE). Lets see why it is the case. MySQL indexes are (with few exceptions) BTREE indexes – this index type is… not know of the hint in MySQL which would allow forcing using index merge when MySQL does not think it should be…
Comment: COUNT(*) for Innodb Tables
February 16, 2009 By 29 Comments
… time it takes to “select count($field) from $table use index($field);” ($field is the primary key (int autoincrement) in $table… count without use index takes around 8 seconds – the count with forced index takes below 0.1 seconds. any thoughts on why this is…
Comment: InnoDB: look after fragmentation
November 9, 2009 By 12 Comments
Vadim in this case / with that special purpose in mind why do you compare the runtimes of different queries? Q1= SELECT…:00′ At first glance one would use identical queries and FORCE INDEX usage, wouldn’t one?
Post: Recovering Innodb table Corruption
July 4, 2008 By 19 Comments
… already) 6353 InnoDB: Page may be an index page where index id is 0 25556 InnoDB: (index “PRIMARY” of table “test”.”test”) InnoDB… may think why do not you simply rebuild table by using OPTIMIZE TABLE ? This is because Running in innodb_force_recovery mode…) > 0 || (level == 0 && page_get_page_no(page) == dict_index_get_page(index)) InnoDB: We intentionally generate a memory trap. InnoDB: Submit…
