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

indexmariadbWordpress

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?

Best Answer

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.

Related Solutions

Should I use an index or a hash function on a primary key if I have 100000 rows of data

With 100k records it shouldn't matter. Even with 100M records it probably won't matter. I've had queries on tables with billions of rows using good old traditional indexes perform just fine.

It's all about how much memory you have and how fast the disks are.

Granted you'll probably get some more specific information if you tell us what database you are using and provide more specifics.

Postgresql – Should I create an index for non key columns

The selection of enough indexes is often difficult. In your case it should be useful to create two indexes.

You should only create one index with both columns if your query always include the first column as a condition:

SELECT * FROM "MATCH" WHERE "OWNER_A_ID" = owner_a_id [AND "OWNER_B_ID" = owner_b_id]

The whole B-Tree is built upon the order of columns in the index! You can't fully use a multi-column index on a, b on the following queries:

SELECT * FROM "MATCH" WHERE "OWNER_A_ID" = owner_a_id OR "OWNER_B_ID" = owner_b_id
SELECT * FROM "MATCH" WHERE "OWNER_A_ID" = "OWNER_B_ID"

If you're only using equality checks you might consider a hash index. But postgresql has some disadvantages you should check first.

On other dbms you should consider adding additional columns in the index as data. This would be useful if you query these specific columns and not * because the dbms wouldn't need to feed the data from the table after using the index.

An important factor: indices fragment over the time (unless you aren't performing any insert/update/delete on the table). Please check whether your dba has some optimization operations installed.

Pleae check the documentation for additional options like FILLFACTOR or partial indexes: http://www.postgresql.org/docs/9.3/static/sql-createindex.html

Best Answer

Related Solutions

Should I use an index or a hash function on a primary key if I have 100000 rows of data

Postgresql – Should I create an index for non key columns

Related Question