How to optimize this query for more meta fields

Question

How can I optimize this query for more meta rows?

SELECT wp_users.ID,
  (
    SELECT GROUP_CONCAT(IF(wp_usermeta.meta_value like '%main%',
     'paid', '') SEPARATOR '')
    FROM wp_usermeta
    WHERE user_id = wp_users.ID
  ) AS metas
FROM wp_users
limit 1;

I have a users table wp_users and a user metadata table wp_usermeta. The wp_usermeta has the schema.

+----------+---------+-----------------------+------------------------------+
| umeta_id | user_id | meta_key              | meta_value                   |
+----------+---------+-----------------------+------------------------------+
| 23367468 |       4 | ac_id                 | 659497                       |
| 23367473 |       4 | ac_tag_id             | 676135                       |
| 23367461 |       4 | admin_color           | fresh                        |
| 23367469 |       4 | cf_key                | xxxxxxxxxxxxxxxx             |
| 23367460 |       4 | comment_shortcuts     | false                        |
| 23367470 |       4 | credits               | 1500                         |
| 23367457 |       4 | description           |                              |
| 23367479 |       4 | dismissed_wp_pointers |                              |
| 23367480 |       4 | expire_on             | 2023-04-13                   |
| 23367455 |       4 | first_name            |                              |
| 23367456 |       4 | last_name             |                              |
| 23367464 |       4 | locale                |                              |
| 23367454 |       4 | nickname              | 12918905                     |
| 23367471 |       4 | plagiarism            | 1500                         |
| 23367481 |       4 | products              | ["demo_product"]             |
| 23367458 |       4 | rich_editing          | true                         |
| 23367463 |       4 | show_admin_bar_front  | true                         |
| 23367459 |       4 | syntax_highlighting   | true                         |
| 23367482 |       4 | total_credits         | 1500                         |
| 23367462 |       4 | use_ssl               | 0                            |
| 23367466 |       4 | wp_capabilities       | a:1:{s:10:"subscriber";b:1;} |
| 23367465 |       4 | wp_iufsr:subscriber   | NULL                         |
| 23367467 |       4 | wp_user_level         | 0                            |
+----------+---------+-----------------------+------------------------------+

My query will become a real burden on the database if I add a new subquery for each meta_key. Is there a way to dynamically create the columns based on the meta key of the metadata table?

reading the docs, google

Are you concerned about typing a long query, or about the response time of it? — The Impaler, Mar 13 '23 at 18:23
Because the metadata table has 23 rows per user (and growing) I'm worried that each of the select statements are going to hammer the database and cause performance problems. — Conor Reedy, Mar 13 '23 at 18:46
The subquery in the example you show is not querying for `meta_key`, it's querying for specific patterns in `meta_value`. So it's difficult to understand what you intend the query to return. Can you show a mocked-up example of what you expect the result of the query to be? — Bill Karwin, Mar 13 '23 at 20:38
@BillKarwin ah, yes. I see where I've been not clear. My desired outcome would take the user metadata table and extend a query for a user and allow me to see the metadata as if it were attached as a column to the user. So if the user had records in the meta table like — Conor Reedy, Mar 13 '23 at 21:12
redo comment- @BillKarwin My desired outcome would take the user metadata table and extend a query for a user and allow me to see the metadata as if it were attached as a column to the user. So if the user had records in the meta table with keys of ac_id, and ac_tag_id the user rows would have those keys as columns. +----------+------------+-------+-----------+ | user_id | user_login | ac_id | ac_tag_id | +----------+------------+-------------------+ | 1 | 4 | 1 | 659497 | +----------+------------+-------+-----------+ ` — Conor Reedy, Mar 13 '23 at 21:20

score 0 · Answer 1 · answered Mar 13 '23 at 21:30

This is basically a pivot-table query:

SELECT u.ID,
  MAX(CASE m.meta_key WHEN 'ac_id' THEN m.meta_value END) AS ac_id,
  MAX(CASE m.meta_key WHEN 'ac_tag_id' THEN m.meta_value END) AS ac_tag_id
  ...similar expressions for other meta attributes...
FROM wp_users AS u
LEFT JOIN wp_usermeta AS m ON u.ID = m.user_id
GROUP BY u.ID;

This is better than the solution you were using with subqueries, because it gets all the values in one pass of the table, regardless of how many attributes you want to display.

Also make sure you have an index on the wp_usermeta table with user_id as its leftmost column.

How to optimize this query for more meta fields

1 Answers1