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.