… brings. Multi Range Read With traditional secondary index lookups, if the columns that are being fetched do not belong to the secondary index definition… that traditional index lookup (for non-index-only columns) involves, reading an index record, and then using the PK column value in the index record to…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… this blog post is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6… is, and what is it aimed at. Index Condition Pushdown Traditional B-Tree index lookups have some limitations in cases such as range scans, where index parts after the part on which range condition is applied…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 30 Comments
… 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… index merge indeed improves performance compared to single index only but it is by far better to use multi column indexes. But the problems with Index…
Post: Multi-Column IN clause - Unexpected MySQL Issue
April 4, 2008 By 20 Comments
… massive amount of urls. To save on indexes instead of using URL we index CRC32 of the URL which allows to find… 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…
Post: 3 ways MySQL uses indexes
September 12, 2009 By 27 Comments
… is not index prefix. It is important to look at key_len column in explain plan to see how many index parts are actually used for row lookup. Very common problem I see is multi column indexes… to couple of columns index can be simply much smaller than the data which is one of the reason covering indexes help to…
Comment: How to find wrong indexing with glance view
August 22, 2008 By 27 Comments
… a 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 more sense…
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, it… situation. I will agree with tgabi, most of the time multi-column indexes are not necessary. If you’re not having issues then… 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
… said:”in case there is good cardinality on both columns and full index can be used”. I’d call that a “best…, 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 work, particular when used for ordering (ORDER BY a,b LIMIT 5). Index…
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… a lot of difference) Saying MySQL can’t use multiple column indexes is simular to saying MySQL always does full table scan…
Comment: Why InnoDB index cardinality varies strangely
September 28, 2009 By 5 Comments
… much worse than it is sounds. For single-column indexes we get variations. For multi-column indexes we have problems. Lets consider CREATE TABLE T… that one of the queries is always executed using wrong index!!! I talked to Percona people. In their opinion the behavior is inherent, as sampling of indexes happen independently. I guess, expectations of optimizer are: 1) x1…
