MySQL Indexes and Keys Create composite index

Help us to keep this website almost Ad Free! It takes only 10 seconds of your time:
> Step 1: Go view our video on YouTube: EF Core Bulk Extensions
> Step 2: And Like the video. BONUS: You can also share it!

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.



Got any MySQL Question?