… well it can deal with primary key appended to the index column, especially in such case as you’re suggesting. In the… does a index be used having an index on one column and using order by on another column. do i need to add the index on column using order… more details. Q: how mysql use index for group by? A: If you have Index on the column MySQL can avoid temporary table or…
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
…indexes.index_name as index_name from all_indexes left join used_indexes using (TABLE_SCHEMA, TABLE_NAME, INDEX_NAME) where used_indexes.INDEX_NAME is NULL and all_indexes.INDEX…
Post: The Optimization That (Often) Isn't: Index Merge Intersection
December 14, 2012 By 6 Comments
…columns that had single-column indexes on them, MySQL could sometimes make use of the multiple indexes. For instance, “SELECT foo FROM bar WHERE indexed…parent_id); Using where; Using index; Using filesort At first glance, this might not look too bad. MySQL is using three different indexes to search …
Post: Tools and Techniques for Index Design Webinar Questions Followup
October 4, 2012 By 5 Comments
… MySQL can use indexes, such as: Index merge improvements. But I expect that defining the right compound index will still be superior. Index condition pushdown… groups by the same column as its sort column. You can optimize by adding the grouping column to your index, so all rows in… its effect on index design? The most important consideration is that “every unique key on the table must use every column in the…
Post: Multiple column index vs multiple indexes
August 22, 2008 By 13 Comments
… with index accesses in general. Note2 Q2/Q3 can’t use Index Merge however as it is currently implemented so they would use single index range scan. The 2 indexes however benefits… surprised why 2 column index is faster for Q3 in case of low cardinality even though MySQL can’t use index well. You’re…
Post: Covering index and prefix indexes
November 23, 2006 By 5 Comments
… can use index which has prefix key parts as covering index if you do not touch columns which only have prefixes in the index. Notice “Using Index” in Extra column. mysql> explain select…
Post: How number of columns affects performance ?
September 28, 2009 By 12 Comments
… with 1 tinyint column and char(99) column and table with 100 tinyint columns. The former two…index | NULL | t1 | 1 | NULL | 12000000 | Using index | +—-+————-+——-+——-+—————+——+———+——+———-+————-+ 1 row in set (0.00 sec) As you can see the index…
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 cannot be used for filtering records. For…
Post: Do you always need index on WHERE column ?
August 28, 2007 By 17 Comments
… clause WHERE has_something=1 we should have index on column `has_something` (the column has two values 0 and 1). In reality… see with index the time is by 3.5 times slower. Good that mysql in this case choose do not use index mysql….62 sec) query with index is still 2 times slower. and this time mysql is going to use index in execution plan: mysql…
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 10 Comments
…index length increases dramatically queries which can use index can also use prefix of the new index…
