… traditional secondary index lookups, if the columns that are being fetched do not belong to the secondary index definition (and hence covering index optimization… of difference when the workload is IO bound, the query time is decreased from ~11min to under a minute. The query… 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: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… point index lookups to one or more index range lookups. This means MySQL can employ many other optimizations like for example if columns other then the secondary key columns… make much of a difference in the query times. Conclusion BKA improves the query time by a huge margin for IO bound…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
… like to see MySQL memory consumption plotted. I use “VSZ” columns from “ps” output on Linux. It helps me to understand…: 0 AVG_ROW_LENGTH: 0 DATA_LENGTH: 16384 INDEX_LENGTH: 0 CREATE_TIME: NULL UPDATE_TIME: NULL *************************** 2. row *************************** SESSION_ID: 7234 TABLE…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 30 Comments
… use second index and hence index merge, what does it turn to ? It is not combined index but single index on another column. This is… this plan is used. Note the query takes about 5 times longer even though in case of full table scan about… combined index in this case. In case you’re using OR between columns – single column indexes are required for index merge to work and combined indexes…
Post: Do you always need index on WHERE column ?
August 28, 2007 By 16 Comments
… clause WHERE has_something=1 we should have index on column `has_something` (the column has two values 0 and 1). In reality… (10.62 sec) query with index is still 2 times slower. and this time mysql is going to use index in execution plan: mysql…) So only in the last case we really need the index on column `has_something`
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 9 Comments
… (0.00 sec) The obvious optimization is to extend index from column (a) to column (a,b) right which will make it faster… | const,const | 120640 | Using index | +—-+————-+———-+——+—————+——+———+————-+——–+————-+ 1 row in set (0.00 sec) Wow. The query runs 30 times faster – not only because… new index to full extent. What is solution ? You can have “redundant” indexes on (a) and (a,b) at the same time. This…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
…default method, where the execution time is proportional to the number of indexes, with fast index creation the time required to copy the …keys ignores indexes on AUTO_INCREMENT columns, because they must be indexed, so it is impossible to temporarily drop the corresponding index; mysqldump –innodb…
Post: Multiple column index vs multiple indexes
August 22, 2008 By 13 Comments
… my previous post there were questions raised about Index Merge on Multiple Indexes vs Two Column Index efficiency. I mentioned in most cases when query can use both of the ways using multiple column index would… too fast if run once I ran them multiple times and measured time appropriately. To remove the overhead of starting MySQL etc…
Post: How number of columns affects performance ?
September 28, 2009 By 10 Comments
…) column and table with 100 tinyint columns. The former two tables have the same row length but have number of column different 50 times. Finally I have created 4th table which is also 100 columns but one of them… stored in BTREE index very similarly to how indexes are stored. Summary: Beware of dynamic row format tables with many columns they might…
