Should a meta_key or other key-value table columns use a HASH or BTREE index?

Question

I've noticed that my WordPress database has BTREE indexes everywhere.

By my understanding, BTREE indexes allow more efficient < > and BETWEEN queries, but are otherwise less efficient than a HASH query for = and != queries.

This makes sense for some columns that might be filtered numerically (dates obviously, and IDs potentially, – though in practice ID > x doesn't seem very likely in WordPress), but I can't see any benefit for columns like meta_key which stores the text key of a key-value pair, or indeed the post_id column of a post_metadata table, which will almost without exception be JOINed to the posts table.

Would a HASH index be more performant? If so, is there any reason that my database is using BTREE indexes for these columns, or is this just a default? If not, what am I lacking in my understanding of indexes?

Answer 1

That's the wrong question. The indexes that WP provides on the meta tables are inefficient. (And the general EAV pattern sucks.)

This explains how to significantly improve the indexes: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta

Back to your question: BTree is about as fast as Hash (were it to exist). And Hash is not available for InnoDB. (InnoDB is the only 'engine' you should use for WP.)

I believe that the developer of InnoDB saw that "one side does fit all" when he implemented only BTree.