[wp-trac] [WordPress Trac] #36866: Add another index to usermeta table

[WordPress Trac]

#36866: Add another index to usermeta table
--------------------------+-----------------------------
 Reporter:  mnelson4      |      Owner:
     Type:  defect (bug)  |     Status:  new
 Priority:  normal        |  Milestone:  Awaiting Review
Component:  Database      |    Version:
 Severity:  normal        |   Keywords:
  Focuses:                |
--------------------------+-----------------------------
 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

--
Ticket URL: <https://core.trac.wordpress.org/ticket/36866>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform