Make WordPress Core

Opened 8 years ago

Closed 8 years ago

Last modified 8 years ago

#36866 closed enhancement (invalid)

Add another index to usermeta table

Reported by: mnelson4's profile mnelson4 Owned by:
Milestone: Priority: normal
Severity: normal Version:
Component: Database Keywords:
Focuses: Cc:

Description

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

Change History (7)

#1 @lukecavanagh
8 years ago

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

#2 @mnelson4
8 years ago

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

#3 @pento
8 years ago

  • Keywords reporter-feedback added

Hi @mnelson4, thanks for the request!

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

#4 @mnelson4
8 years ago

  • Type changed from defect (bug) to enhancement

This particular query is from a plugin, not from core

#5 @pento
8 years ago

  • Keywords reporter-feedback removed
  • Milestone Awaiting Review deleted
  • Resolution set to invalid
  • Status changed from new to closed

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.

#6 @mnelson4
8 years ago

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.

#7 @pento
8 years ago

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.

Note: See TracTickets for help on using tickets.