Create composite index

Download mysql eBook

Example

This will create a composite index of both keys, mystring and mydatetime and speed up queries with both columns in the WHERE clause.

CREATE INDEX idx_mycol_myothercol ON my_table(mycol, myothercol)

Note: The order is important! If the search query does not include both columns in the WHERE clause, it can only use the leftmost index. In this case, a query with mycol in the WHERE will use the index, a query searching for myothercol without also searching for mycol will not. For more information check out this blog post.

Note: Due to the way BTREE's work, columns that are usually queried in ranges should go in the rightmost value. For example, DATETIME columns are usualy queried like WHERE datecol > '2016-01-01 00:00:00'. BTREE indexes handle ranges very efficiently but only if the column being queried as a range is the last one in the composite index.

Stats

Contributors: 1
2016-07-23
Licensed under: CC-BY-SA

Not affiliated with Stack Overflow
Rip Tutorial: info@zzzprojects.com

Download eBook