…column, pk_column FROM tbl WHERE key_column=x ORDER BY key_column (Note that secondary keys in InnoDB contain primary key columns) Buffer each pk_column… 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: 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… by col1 i.e. in index order and then the lookup will be performed. So key-ordered scan is basically an extension of…
Comment: What does Using filesort mean in MySQL?
March 15, 2012 By 27 Comments
… rows. This query uses index (type: index, key: a, key_len: 103, Extra: ”): EXPLAIN SELECT * FROM table1 ORDER BY a LIMIT 1698, 1… index but it does not. I just see this note: With EXPLAIN SELECT … ORDER BY, you can check whether MySQL can use indexes to resolve the query. It cannot if you see Using filesort in the Extra column.
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 93 Comments
… last column, in fact index can be used for ORDER BY if sorting is done by leading column(s). Note however columns following column used for order… table. If ORDER BY is going by field from the table which is not first in the join order index can’t be used. Sometimes it means breaking normalization and duplicating column(s) you’re going to use in ORDER BY…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 30 Comments
… values in sorted order. For example when you query AGE=18 with single column BTREE index MySQL will dive into the index to find first matching row and when will continue scanning index in order until… 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: A rule of thumb for choosing column order in indexes
June 5, 2009 By 15 Comments
… of thumb I sometimes use to decide which columns should come first in an index. This is not specific to MySQL, it’s generally applicable to any database server with b-tree indexes. And there are a bunch of subtleties, but I will…
Post: Duplicate indexes and redundant indexes
August 17, 2006 By 38 Comments
… same column(s) – perfect example is BTREE index and FULLTEXT index, while other combinations may also make sense. Note: Order of columns in index is significant, index (A,B) is not duplicate to index…
Post: Should you name indexes while doing ALTER TABLE ?
May 28, 2008 By 10 Comments
… the indexes. Lets first speak about naming. If you do not specify index name MySQL will name index by the first column of index created, if there is such index already it will… on order you add indexes indexes can get different names which makes scripted upgrade and downgrade processes complicated. If you use auto generated index…
Comment: How to find wrong indexing with glance view
March 8, 2010 By 27 Comments
… I need an index on order_date to help with finding orders for a given date. 8 columns 6 indexes on single columns. This is silly. You can imagine a table with more columns and FKs and more indexes. Sure…
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…=5 ORDER BY B ; A=5 ORDER BY B DESC; A>5 ORDER BY A ; A>5 ORDER BY A,B ; A>5 ORDER BY… to couple of columns index can be simply much smaller than the data which is one of the reason covering indexes help to…
