Add another index to usermeta table

[mnelson4]

On a large multisite WP install (eventsmart.com) we found this query

SELECT user_id from ssusermeta WHERE meta_key='primary_blog' AND meta_value='3104' ORDER BY user_id ASC;

was a really slow query (got thousands of users) and it WASN'T using any index. The pageloads that were using this query taking about 10-15 seconds for us. However, if we added the following index

create index meta_key_user_id on ssusermeta(meta_key, user_id);

it reduced these particular pageloads to 3-4 seconds.

So in general adding this index would speed up queries where both meta_key and user_id are used

[lukecavanagh]

Would this also give the same increase on single sites with high user levels as well?

[mnelson4]

yeah that's true, any site with lots of users would find queries using the meta key and user id would be sped up significantly, I would think. I haven't tested that

[pento]

Hi @mnelson4, thanks for the request!

Is this a WordPress Core query, or is it coming from a plugin or theme?

[mnelson4]

This particular query is from a plugin, not from core

[pento]

Thanks for the info, @mnelson4. I'd suggest contacting the plugin author to ask them about adding the index through their plugin - adding an index to all WordPress sites just for one plugin isn't really an option.

If the plugin author chooses to go that route, I strongly recommend they use the plugin slug in the index name, so that there's no chance of this index colliding with any future indexes we may add to WordPress Core.

[mnelson4]

kk @pento I'll let you know if I see this would benefit WP core directly, ie if WP cores queries would be sped up by this index (my original thought was that this index would benefit any sites running similar queries, but I acknowledge I don't have solid evidence of that).
Also I didn't realize it's kosher for a plugin to add indexes to WP core tables.

[pento]

I don't generally encourage plugins to add new indexes (I'd much prefer a query rewrite to make better use of existing indexes), but in this case, it's likely that a new index will be the best option. Indexes can be added to core tables, as long as they're done so responsibly.

[curtisfraser]

Adding the index within core is a good idea. Efficiency is a good thing.

I would go a little further though. The combination user_id+meta_key should be unique, therefore a key should be created, which is then indexed.

However the size of meta_key(255) poses a problem. This should be set to 150. Then regardless of data type, a key can be created and it can be indexed.

Then plugins can use a simple SQL for insert/update:

INSERT INTO wp_usermeta (user_id, meta_key, meta_value)
VALUES ('UserID', 'your_meta_key', 'your_value')
ON DUPLICATE KEY UPDATE meta_value = VALUES(meta_value);