Search code examples
mysqlsqlindexingb-tree-index

MySQL - BTree AND Hash indexes for the same column

I have tried finding similar questions but didn't find any, except questions regarding two indexes for the same column (in general).

Let us assume we have a table with a column COL. The table (and the entire database) is read-only for clients (let us assume it is updated once/once every long periods of time and only by a backend service). Therefore, insertion/update time is irrelevant.

For this column, there are highly used queries which search for rows where the COL value is in some range, and some more highly used queries that search for rows where COL is compared directly with a value (equality check).

Is it beneficial, giving the above, to hold both a BTREE and HASH index on COL? Will the optimizer use the BTREE index for the range queries and the HASH index for the direct comparison queries? Will the answer change if COL is of type varchar(256)?

Thanks!

Solution

  • I would stick with the B-Tree index, because you need it anyway. There could possibly be some minor performance gain from using a hash index as well, but you are also going to be occupying more memory.

    More importantly, hash indexes are not available for the most common storage engines (InnoDB and MyISAM) -- Table 13.1 in the documentation.