[wp-trac] [WordPress Trac] #55749: Large-site inefficiency in REST users endpoint used by Gutenberg editor

[WordPress Trac]

#55749: Large-site inefficiency in REST users endpoint used by Gutenberg editor
--------------------------+------------------------------------
 Reporter:  OllieJones    |       Owner:  (none)
     Type:  defect (bug)  |      Status:  new
 Priority:  normal        |   Milestone:  Awaiting Review
Component:  Users         |     Version:  trunk
 Severity:  normal        |  Resolution:
 Keywords:                |     Focuses:  rest-api, performance
--------------------------+------------------------------------

Comment (by rjasdfiii):

 Please measure and report the time spent in REST processing versus the
 time spent running the SELECT.  (We should not waste time chasing the
 wrong thing.)

 Ollie gave an in-depth discussion of why that SELECT is inefficient.  Let
 me continue that discussion.  Here are some steps to take to speed up a
 query such as that one:

 1. A better INDEX -- That will help some, but there are structural issues
 with the table that prevent a significant improvement.  Ollie and I
 provide a Plugin that updates the schemas to have better indexes.
 2. Reformulate the query. -- Perhaps the only easy fix is to get rid of
 the SQL_CALC_FOUND_ROWS.  By the way, that feature is being removed in
 MySQL 8.0.  In the future, WP will have to either abandon getting the
 count, or run a separate query to get the count.
 3. Redesign the schema. -- WP is based on the simple and flexible "Entity-
 Attribute-Value" schema pattern ("EAV").  It is great for small datasets.
 But for large tables, it performs miserably.

 EAV...

 * Entity:  post_id, user_id, etc  (depending on which set of tables)
 * Attribute:  xx_meta_key
 * Value:  xx_meta_value -- Note when this holds a number and you want to
 test a range of numeric values, this LONGTEXT needs to be converted to
 numeric for the test.  This is another case where EAV performs miserably.

 We would like our indexes to be folded into WP (and Woo) Core.

 Unfortunately, the next level of performance improvement means abandoning
 EAV.  There is no "simple and straightforward" replacement for EAV.

-- 
Ticket URL: <https://core.trac.wordpress.org/ticket/55749#comment:4>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform