… and what benefits it brings. Multi Range Read With traditional secondary index lookups, if the columns that are being fetched do not… key_column, pk_column FROM tbl WHERE key_column=x ORDER BY key_column (Note that secondary keys in InnoDB contain primary key columns) For… key_column, pk_column FROM tbl WHERE key_column=x ORDER BY key_column (Note that secondary keys in InnoDB contain primary key columns) Buffer…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
I have been working with Peter in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a look at and benchmarking optimizer enhancements one by one. So in the same way this blog post is aimed at a new optimizer enhancement Index Condition …
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 30 Comments
… separate indexes on AGE and STATE columns. The better strategy is often to have combined multi-column index on (AGE,STATE). Lets see… index only but it is by far better to use multi column indexes. But the problems with Index Merge do not stop…) As a summary: Use multi column indexes is typically best idea if you use AND between such columns in where clause. Index merge…
Post: Multi-Column IN clause - Unexpected MySQL Issue
April 4, 2008 By 20 Comments
… in this case, even though the cardinality on the first column is almost perfect. I did some more testing and it… missing optimizer feature. I should not be surprised though as multi-column in is not the most used MySQL feature out there… case we could simply rewrite query using more standard single column IN clause: mysql> explain SELECT url FROM 106pages.106pages WHERE…
Comment: How to find wrong indexing with glance view
August 22, 2008 By 27 Comments
… definitely want to keep those 3 additional columns in your index. When that 4th extra column moves from millions to 20 records… situation. I will agree with tgabi, most of the time multi-column indexes are not necessary. If you’re not having issues… to help others by making aware reasons for particular solutions (multi-column index in this case) in MySQL.
Comment: How to find wrong indexing with glance view
August 22, 2008 By 27 Comments
… few columns from that index without losing any performance. And there’s the problem of mysql choosing single column indexes where multi-column indexes are present, and wasting memory and disk space. I still think that single column indexes make…
Comment: How to find wrong indexing with glance view
August 22, 2008 By 27 Comments
…, You said:”in case there is good cardinality on both columns and full index can be used”. I’d call that… line of work I’m searching trough many low cardinality columns in the same query. The query is very specific, yet each column has only few values.Sometimes I wish I can find a multi-column index that works. I have few multi-column that do…
Comment: How to find wrong indexing with glance view
August 22, 2008 By 27 Comments
… Will only use only first column of multiple column index in case you have Range on the first column like col1 BETWEEN 5 and 10 – it is exactly skill of creating multi column indexes to ensure they… a lot of difference) Saying MySQL can’t use multiple column indexes is simular to saying MySQL always does full table…
Post: 3 ways MySQL uses indexes
September 12, 2009 By 27 Comments
… index prefix. It is important to look at key_len column in explain plan to see how many index parts are… used for row lookup. Very common problem I see is multi column indexes which are used but only to their short prefix… of that if you just need access to couple of columns index can be simply much smaller than the data which…
Comment: MySQL Full Text Search in Action
January 12, 2010 By 9 Comments
… other complement, should i also have individual indexes, or only multi column? 1) fulltext(main),fulltext(first),fulltext(alternative),fulltext(main,first… faster for search to have also index on only main column?
