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?

  • take a look at this Stackoverflow post stackoverflow.com/questions/7306316/b-tree-vs-hash-table and see the constraints on primary keys – nbk Nov 19 '20 at 21:35
  • @nbk thanks, that’s helpful in explaining the differences, but I lack enough understanding of DB performance to be sure I’m applying it correctly. Given hashes are O(1), and a key-value table is unlikely to be queried other than for equality, what are the pros and cons? – Dan W Nov 19 '20 at 21:54
  • i believe meta_key is not a primary key, so hashes are out of the question, that was my hint at the end. Hashes need a lot of space to map them, that was explained in the post. also it takes a overhead to calculate the hash. using hashes because of the constraints is rare, and the balanced binary trees are the best till now. also HASH is only supported by the MEMORY storage engine. another constraint which disqualifies it – nbk Nov 19 '20 at 22:19
  • Thanks, that’s probably the answers I need. – Dan W Nov 19 '20 at 22:47

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.