0

I have a query written out to search a WordPress database for any post ids that match the keyword search to post content, title, author, or associated company. The company portion of the query is

SELECT DISTINCT p.id FROM wp_posts AS p, wp_postmeta AS pm 
                        WHERE post_type='publication' 
                        AND p.id=pm.post_id
                        AND meta_key LIKE 'authors_%_name' 
                        AND meta_value IN (
                            SELECT DISTINCT p.id FROM wp_posts AS p, wp_postmeta AS pm 
                            WHERE post_type='authors' 
                            AND meta_key='company' 
                            AND p.id=pm.post_id 
                            AND meta_value IN (
                                SELECT DISTINCT pm.meta_value FROM wp_postmeta as pm, wp_terms AS t 
                                WHERE meta_key='company' 
                                AND pm.meta_value=t.term_id 
                                AND name LIKE '%".$_GET['phrase']."%'
                                )
                            )

This runs for 8-10 seconds, but if I make a minor change in the last sub-query from

SELECT DISTINCT pm.meta_value FROM wp_postmeta as pm, wp_terms AS t 
                              WHERE meta_key='company' 
                              AND pm.meta_value=t.term_id 
                              AND name LIKE '%".$_GET['phrase']."%'

to

SELECT DISTINCT pm.meta_value FROM wp_postmeta, wp_terms AS t 
                              WHERE meta_key='company' 
                              AND pm.meta_value=t.term_id 
                              AND name LIKE '%".$_GET['phrase']."%'

and remove the defined alias, the query takes .1-.3 seconds to process.
What causes this behavior?

Also, if this entire query is sub-optimal, it is because I wrote it while trying to explore the WordPress schema, so I'm open to any suggestions to clean it up.

Here are the two EXPLAIN plans:

1) With alias "as pm" present on line 11

+----+--------------------+-------+--------+--------------------------+----------+---------+-------------------------------+------+------------------------------+
| id | select_type        | table | type   | possible_keys            | key      | key_len | ref                           | rows | Extra                        |
+----+--------------------+-------+--------+--------------------------+----------+---------+-------------------------------+------+------------------------------+
|  1 | PRIMARY            | pm    | range  | post_id,meta_key         | meta_key | 576     | NULL                          | 3258 | Using where; Using temporary |
|  1 | PRIMARY            | p     | eq_ref | PRIMARY,type_status_date | PRIMARY  | 8       | gtisoft_staging.pm.post_id    |    1 | Using where                  |
|  2 | DEPENDENT SUBQUERY | p     | eq_ref | PRIMARY,type_status_date | PRIMARY  | 8       | func                          |    1 | Using where; Using temporary |
|  2 | DEPENDENT SUBQUERY | pm    | ref    | post_id,meta_key         | post_id  | 8       | func                          |   19 | Using where; Distinct        |
|  3 | DEPENDENT SUBQUERY | pm    | ref    | meta_key                 | meta_key | 576     | const                         | 3055 | Using where; Using temporary |
|  3 | DEPENDENT SUBQUERY | t     | eq_ref | PRIMARY                  | PRIMARY  | 8       | gtisoft_staging.pm.meta_value |    1 | Using where; Distinct        |
+----+--------------------+-------+--------+--------------------------+----------+---------+-------------------------------+------+------------------------------+

2) Without "AS pm" alias removed from line 11

+----+--------------------+-------------+--------+--------------------------+----------+---------+-------------------------------+------+------------------------------+
| id | select_type        | table       | type   | possible_keys            | key      | key_len | ref                           | rows | Extra                        |
+----+--------------------+-------------+--------+--------------------------+----------+---------+-------------------------------+------+------------------------------+
|  1 | PRIMARY            | pm          | range  | post_id,meta_key         | meta_key | 576     | NULL                          | 3258 | Using where; Using temporary |
|  1 | PRIMARY            | p           | eq_ref | PRIMARY,type_status_date | PRIMARY  | 8       | gtisoft_staging.pm.post_id    |    1 | Using where                  |
|  2 | DEPENDENT SUBQUERY | p           | eq_ref | PRIMARY,type_status_date | PRIMARY  | 8       | func                          |    1 | Using where; Using temporary |
|  2 | DEPENDENT SUBQUERY | pm          | ref    | post_id,meta_key         | post_id  | 8       | func                          |   19 | Using where; Distinct        |
|  3 | DEPENDENT SUBQUERY | t           | eq_ref | PRIMARY                  | PRIMARY  | 8       | gtisoft_staging.pm.meta_value |    1 | Using where; Using temporary |
|  3 | DEPENDENT SUBQUERY | wp_postmeta | ref    | meta_key                 | meta_key | 576     | const                         | 3055 | Using where                  |
+----+--------------------+-------------+--------+--------------------------+----------+---------+-------------------------------+------+------------------------------+
  • 1
    Please post the exact code change. If you can post the two EXPLAIN plans, it would be good, too. – ypercubeᵀᴹ Aug 4 '15 at 16:00
  • 1
    Can you post the result of SHOW CREATE TABLE <My_Tables>\G where <My_Tables> are all of the tables accessed during the query. – Vérace Aug 4 '15 at 16:13
  • @ypercube I added the EXPLAIN plans and showed the exact code change that happens on line 11. – MooseBalm Aug 4 '15 at 16:35
  • I don't have time to answer now but the change you did, changes the query - and possibly the results. The AND pm.meta_value=... in the 2nd query uses the pm from the above (second) level. In the first query, the pm is instead related to the immediate (third) level. – ypercubeᵀᴹ Aug 4 '15 at 17:06
1

I'm surprised that this query actually works. You're using the same aliases three times. You can access the outer tables in a subquery, you know? That's one of the reasons why aliases are actually there, to distinguish tables when used multiple times.

First I would recommend, that you're using the join syntax introduced with the ANSI SQL standard from 1992 which works like this:

SELECT DISTINCT p.id 
FROM wp_posts AS p 
INNER JOIN wp_postmeta AS pm ON p.id = pm.post_id
...

It becomes more readable and you have the join clauses separated from the filtering in the where clause.

Instead of writing more and more subqueries, you could also just join again. Something like this (not sure if correct):

SELECT DISTINCT p.id 
FROM wp_posts AS p 
INNER JOIN wp_postmeta AS pm ON p.id = pm.post_id
-- author
INNER JOIN wp_posts p_author ON pm.meta_value = p_author.id
INNER JOIN wp_postmeta pm_author ON p_author.id = pm_author.post_id
-- company
INNER JOIN wp_postmeta pm_company ON pm_author.meta_value = pm_company.meta_value
INNER JOIN wp_terms terms_company ON pm_company.meta_value = terms_company.term_id
WHERE TRUE
-- 
AND p.post_type = 'publication' 
AND pm.meta_key LIKE 'authors_%_name' 
-- author
AND p_author.post_type = 'authors'
AND pm_author.meta_key = 'company'
-- company
AND pm_company.meta_key = 'company'
AND pm_company.name LIKE '%".$_GET['phrase']."%'
| improve this answer | |
  • The query is valid. The internal aliases are not exposed in the external levels and the external aliases are not used in the internal levels. The query doesn't need that, as you pointed (it's using IN and no correlation). – ypercubeᵀᴹ Aug 4 '15 at 16:08
  • Thank you for the clarification. I will try to apply this to my database, but the WordPress schema is sort of a mess for the purpose of this query. All authors and publications are kept in the same table as users, pages, and other posts. They all have their metadata kept in a single metadata table, and the companies are all defined in the terms table that is pointed to by the metadata table. – MooseBalm Aug 4 '15 at 16:22
0

Looks like in the name of alises you also seem to ignoring the join wp_postmeta as pm, wp_terms AS t!

So my guess is non-existence of index is causing the delay, but you can confirm by posting explain plan and/or table definitions.

SELECT DISTINCT pm.meta_value FROM wp_postmeta as pm, wp_terms AS t 
 WHERE meta_key='company' 
 AND pm.meta_value=t.term_id 

Confirm that the meta_value and term_id are indexed in both the tables. If not, indexing them should be faster.

| improve this answer | |
0

You have 3 different instances of tables aliased as pm. Your "minor" change changed from using one instance to another.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.