[wp-trac] [WordPress Trac] #45354: Adding index for meta_key and post_id in postmeta

[WordPress Trac]

#45354: Adding index for meta_key and post_id in postmeta
-------------------------------------------------+-------------------------
 Reporter:  DuckDagobert                         |       Owner:  (none)
     Type:  enhancement                          |      Status:  new
 Priority:  normal                               |   Milestone:  7.1
Component:  Database                             |     Version:
 Severity:  normal                               |  Resolution:
 Keywords:  needs-testing dev-feedback has-      |     Focuses:
  patch has-unit-tests                           |  performance
-------------------------------------------------+-------------------------

Comment (by ozgursar):

 == Patch Testing Report

 Patch Tested: https://github.com/WordPress/wordpress-develop/pull/10987

 === Environment
 - WordPress: 7.1-alpha-62161-src
 - PHP: 8.2.29
 - Server: nginx/1.29.4
 - Database: mysqli (Server: 8.4.7 / Client: mysqlnd 8.2.29)
 - Browser: Chrome 148.0.0.0
 - OS: macOS
 - Theme: Twenty Twenty-Five 1.5
 - MU Plugins: None activated
 - Plugins:
   * Code Snippets 3.9.6
   * Test Reports 1.2.1

 I created a benchmark tool using Claude that seeds the db and applies the
 key in the patch.

 === Steps taken
 1. Add the code snippet to your active theme's functions.php or using Code
 Snippets plugin from the GitHub Gist
 https://gist.github.com/ozgursar/a798c60cf3f8391de37895658e5e4c80
 2. Navigate to ''Tools > Postmeta Benchmark''
 3. Click ''Seed data'' to add 500 posts and 7500 meta rows to the database
 4. Click ''Run benchmark''
 5. Observe the keys and results
 6. Apply the patch
 7. Click `Apply schema (dbDelta)` to ensure composite key in the patch is
 applied to the db
 8. Run benchmark again and observe the key sizes and results.
 9. ✅ Patch is solving the problem

 === Expected result
 - The `Double JOIN` query should show the largest improvement after
 applying the patch, as it is the pattern most affected by the missing
 composite index.

 - MySQL previously had to scan all postmeta rows for each join condition
 separately. The `meta_key_id` index should appear in the active indexes
 list and the Size per index table after running Apply schema (dbDelta).

 === Additional Notes
 - The benchmark applies the schema using dbDelta( `wp_get_db_schema()` ),
 so it tests the exact index defined in the patch.

 === Screenshots/Screencast with results
 Before patch
 [[Image(https://core.trac.wordpress.org/raw-
 attachment/ticket/45354/before.png)]]

 Seeding DB and Applying Schema
 [[Image(https://core.trac.wordpress.org/raw-attachment/ticket/45354/seed-
 and-schema.jpg)]]

 After patch
 [[Image(https://core.trac.wordpress.org/raw-
 attachment/ticket/45354/after.png)]]

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