…. Multi Range Read With traditional secondary index lookups, if the columns that are being fetched do not belong to the secondary index definition…. Suppose you have the following query executed on the InnoDB table: SELECT non_key_column FROM tbl WHERE key_column=x… traditional index lookup (for non-index-only columns) involves, reading an index record, and then using the PK column value in the index record…
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
… 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… compared to single index only but it is by far better to use multi column indexes. But the problems with Index Merge do not… intersection) on values it gets from the single table. I have reverted table to have i1 and i2 as independent columns in…
Post: Performance Schema tables stats
May 2, 2011 By 2 Comments
… memory workload and against single tables. Now after adding multi-tables support to sysbench, it …table with statistic per index: mysql> select * from table_io_waits_summary_by_index_usage where OBJECT_NAME=’sbtest55′G *************************** 1. row *************************** OBJECT_TYPE: TABLE…
Post: 3 ways MySQL uses indexes
September 12, 2009 By 27 Comments
… see how many index parts are actually used for row lookup. Very common problem I see is multi column indexes which are used… on the table. In such case you would see “Index” type in explain which correspond to scanning (potentially) complete table in the index order. It is very important to understand in which conditions index can be used to…
Post: Sysbench with support of multi-tables workload
April 29, 2011 By 4 Comments
… select insert delete update_index update_non_index to support different OLTP modes. Most interesting: all scripts support –oltp-tables-count=N ( default 1), in this case N sbtest tables…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 139 Comments
… (ie old and rarely accessed data stored in different servers), multi-server partitioning to use combined memory and a lot of… to work with in temporary table etc. Prefer full table scans to index accesses – For large data sets full table scans are often faster than range scans and other types of index…
Comment: How to find wrong indexing with glance view
August 22, 2008 By 27 Comments
… I remove and that had performance consequences. I join 2 tables to together plus my WHERE is a bit more complicated…. 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.
Post: Multi-Column IN clause - Unexpected MySQL Issue
April 4, 2008 By 20 Comments
…amount of urls. To save on indexes instead of using URL we index CRC32 of the URL which allows to…multiple column IN makes MySQL to pick doing full table scan in this case, even though the cardinality …optimizer feature. I should not be surprised though as multi-column in is not the most used MySQL …
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
… own clients, with no delays. Finally, the analysis is usually multi-dimensional. The typical user wants to be able to… will every index be a little narrower, the table will now contain only half as many rows. That will make the indexes less than half the size. In real life this technique often makes the table+index much less than half…
