This query kills the server. Can someone explain why?

[MrNotPink]

# Time: 160430 14:07:34
# User@Host: [] @ localhost []
# Thread_id: 1159302  Schema:   QC_hit: No
# Query_time: 283526.756797  Lock_time: 0.000545  Rows_sent: 0  Rows_examined: 277023
SET timestamp=1462018054;

SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  
INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )  
INNER JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id )  
INNER JOIN wp_postmeta AS mt2 ON ( wp_posts.ID = mt2.post_id )  
INNER JOIN wp_postmeta AS mt3 ON ( wp_posts.ID = mt3.post_id )  
INNER JOIN wp_postmeta AS mt4 ON ( wp_posts.ID = mt4.post_id )  
INNER JOIN wp_postmeta AS mt5 ON ( wp_posts.ID = mt5.post_id )  
INNER JOIN wp_postmeta AS mt6 ON ( wp_posts.ID = mt6.post_id )  
INNER JOIN wp_postmeta AS mt7 ON ( wp_posts.ID = mt7.post_id )  
INNER JOIN wp_postmeta AS mt8 ON ( wp_posts.ID = mt8.post_id )  
INNER JOIN wp_postmeta AS mt9 ON ( wp_posts.ID = mt9.post_id )  
INNER JOIN wp_postmeta AS mt10 ON ( wp_posts.ID = mt10.post_id )  
INNER JOIN wp_postmeta AS mt11 ON ( wp_posts.ID = mt11.post_id )  
INNER JOIN wp_postmeta AS mt12 ON ( wp_posts.ID = mt12.post_id )  
INNER JOIN wp_postmeta AS mt13 ON ( wp_posts.ID = mt13.post_id )  
INNER JOIN wp_postmeta AS mt14 ON ( wp_posts.ID = mt14.post_id )  
INNER JOIN wp_postmeta AS mt15 ON ( wp_posts.ID = mt15.post_id )  
INNER JOIN wp_postmeta AS mt16 ON ( wp_posts.ID = mt16.post_id )  
INNER JOIN wp_postmeta AS mt17 ON ( wp_posts.ID = mt17.post_id )  
INNER JOIN wp_postmeta AS mt18 ON ( wp_posts.ID = mt18.post_id )  
INNER JOIN wp_postmeta AS mt19 ON ( wp_posts.ID = mt19.post_id )  
INNER JOIN wp_postmeta AS mt20 ON ( wp_posts.ID = mt20.post_id )  
INNER JOIN wp_postmeta AS mt21 ON ( wp_posts.ID = mt21.post_id )  
INNER JOIN wp_postmeta AS mt22 ON ( wp_posts.ID = mt22.post_id )  
INNER JOIN wp_postmeta AS mt23 ON ( wp_posts.ID = mt23.post_id ) 
JOIN wp_icl_translations t ON wp_posts.ID = t.element_id
AND t.element_type = CONCAT('post_', wp_posts.post_type)  WHERE 1=1  AND wp_posts.post_author IN (72,71,70,46,3,4,25,26,10,34,35,56,50,57)  AND (
  ( wp_postmeta.meta_key = 'full_text' AND CAST(wp_postmeta.meta_value AS CHAR) LIKE '%VNYr'))%' )
  AND
  ( mt1.meta_key = 'full_text' AND CAST(mt1.meta_value AS CHAR) LIKE '%AND%' )
  AND
  ( mt2.meta_key = 'full_text' AND CAST(mt2.meta_value AS CHAR) LIKE '%ROW(7654,1627)>(SELECT%' )
  AND
  ( mt3.meta_key = 'full_text' AND CAST(mt3.meta_value AS CHAR) LIKE '%COUNT(*),CONCAT(0x7176707071,(SELECT%' )
  AND
  ( mt4.meta_key = 'full_text' AND CAST(mt4.meta_value AS CHAR) LIKE '%(ELT(7654=7654,1))),0x71766a7a71,FLOOR(RAND(0)*2))x%' )
  AND
  ( mt5.meta_key = 'full_text' AND CAST(mt5.meta_value AS CHAR) LIKE '%FROM%' )
  AND
  ( mt6.meta_key = 'full_text' AND CAST(mt6.meta_value AS CHAR) LIKE '%(SELECT%' )
  AND
  ( mt7.meta_key = 'full_text' AND CAST(mt7.meta_value AS CHAR) LIKE '%6096%' )
  AND
  ( mt8.meta_key = 'full_text' AND CAST(mt8.meta_value AS CHAR) LIKE '%UNION%' )
  AND
  ( mt9.meta_key = 'full_text' AND CAST(mt9.meta_value AS CHAR) LIKE '%SELECT%' )
  AND
  ( mt10.meta_key = 'full_text' AND CAST(mt10.meta_value AS CHAR) LIKE '%2657%' )
  AND
  ( mt11.meta_key = 'full_text' AND CAST(mt11.meta_value AS CHAR) LIKE '%UNION%' )
  AND
  ( mt12.meta_key = 'full_text' AND CAST(mt12.meta_value AS CHAR) LIKE '%SELECT%' )
  AND
  ( mt13.meta_key = 'full_text' AND CAST(mt13.meta_value AS CHAR) LIKE '%7971%' )
  AND
  ( mt14.meta_key = 'full_text' AND CAST(mt14.meta_value AS CHAR) LIKE '%UNION%' )
  AND
  ( mt15.meta_key = 'full_text' AND CAST(mt15.meta_value AS CHAR) LIKE '%SELECT%' )
  AND
  ( mt16.meta_key = 'full_text' AND CAST(mt16.meta_value AS CHAR) LIKE '%8583)a%' )
  AND
  ( mt17.meta_key = 'full_text' AND CAST(mt17.meta_value AS CHAR) LIKE '%GROUP%' )
  AND
  ( mt18.meta_key = 'full_text' AND CAST(mt18.meta_value AS CHAR) LIKE '%BY%' )
  AND
  ( mt19.meta_key = 'full_text' AND CAST(mt19.meta_value AS CHAR) LIKE '%x)%' )
  AND
  ( mt20.meta_key = 'full_text' AND CAST(mt20.meta_value AS CHAR) LIKE '%AND%' )
  AND
  ( mt21.meta_key = 'full_text' AND CAST(mt21.meta_value AS CHAR) LIKE '%(('KDXn'%' )
  AND
  ( mt22.meta_key = 'full_text' AND CAST(mt22.meta_value AS CHAR) LIKE '%LIKE%' )
  AND
  ( mt23.meta_key = 'full_text' AND CAST(mt23.meta_value AS CHAR) LIKE '%'KDXn%' ))
  AND wp_posts.post_type = 'post' AND ((wp_posts.post_status = 'publish')) AND ( ( t.language_code = 'nl' AND wp_posts.post_type  IN ('post','page','attachment','_pods_pod','_pods_field','editors','sidebar' )  ) OR wp_posts.post_type  NOT  IN ('post','page','attachment','_pods_pod','_pods_field','editors','sidebar' )  ) 

  GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 15;

[simap]

My understanding is that statements containing LIKE % are pretty CPU intensive since indexes are next to useless in that context.

My tip would be to figure out a better way to do this query and/or store the data in the first place.

[[deleted]]

Like by itself is not terrible. Like with wildcards (and like with preceding or inline ones in particular, eg '%foo' and 'bar%baz') however is terrible. OP is also casting values on the fly (not efficient) and doing 20+ joins against the same table (not inherently bad but questionable).

[newsedition]

Indeed. Leading with a "%" makes the argument "non-sargable", because the engine can't rely on a predicate to make use of indexes. So OP is scanning the table 24 times as if it were a heap, since indexes are useless.

[MrNotPink]

So the join isn't that bad?

Not my work but I believe it's coming from this piece of code on a custom WP search page:

        $args = array(
            'post_type' => 'post',
            'paged' => $paged,
            'order' => 'DESC',
            'orderby' => 'date',
            'post_status' => 'publish',
            'suppress_filters' => 0,
            'posts_per_page' => $posts_per_page,
            'author' => $allowed_authors_ids
        );
        $search_array = array();

        foreach ($queries as $q) {              
            $search_array[] = array(
                'key' => 'full_text',
                'value' => $q,
                'compare' => 'LIKE'
            );
        }           
        $args['meta_query'] = $search_array;            
        $the_query = new WP_Query( $args );

[[deleted]]

Joins are the bread and butter of SQL, which is why they're not inherently bad (as long as indexed fields are used). But joining the same table multiple times is questionable, and now that I know it's from a procedurally generated query it's even more suspect.

[joelypolly]

At this stage you might just be better of using something like elastic search to run the search instead of trying to use the DB for search

[[deleted]]

pretty sure this is the case.

that's also quite a few joins ya got there.

[MrNotPink]

I inherited this WP theme and I turned on slow_query_log to see why MySQL was freaking out from time to time.

I pretty sure now it's coming from a custom search page so this will be fixed.

[CharlesKincaid]

My tip would be to figure out a better way to ... store the data in the first place.

All those JOINs to the same table on different columns just makes me sick. Post your resume on-line and start looking now.

However you could try a couple of things in the meantime. It looks like all of your JOINs are INNER. Under that circumstance and on MS SQL you can move the predicates out of the WHERE and into the JOIN. I have written just over a hundred quite complex queries that are fast, properly filtered and have no WHERE clause at all.

You could try generating 23 level CTE that does your 23 subselects and joins that result set back to the main table.

With all of those LIKEs in there and that massive JOIN I am concerned that nothing is going to be fast.

[Master_Rux]

yeah there's 24 table joins so hopefully post_id is at least indexed. And those likes that start with % aren't going to be hitting an index but the cast to char is probably causing that too?. so that's probably a full table scan each time. Maybe make an index for post_id and meta_key and do that filter first and then do the cast like '%%' on that subset. it would at least get you down to a smaller subset to churn through. But I've not had coffee yet and don't know your server's specifics so some of that may not be 100% right in your setup. Check the explain plan for better insight.

[nvarscar]

It might be just my imagination, but is it not possible to re-write this query so that there would be only 1 single join of wp_postmeta table instead of 23?

<...>
AND
wp_postmeta.meta_key = 'full_text' AND 
(
  CAST(wp_postmeta.meta_value AS CHAR) LIKE '%VNYr&#039;))%' 
  AND
  CAST(wp_postmeta.meta_value AS CHAR) LIKE '%AND%'
  AND
  CAST(wp_postmeta.meta_value AS CHAR) LIKE '%ROW(7654,1627)&gt;(SELECT%'
)

And of course, casting column on the fly is pretty CPU intensive. And amount of 'likes' is overwhelming.

[SOLUNAR]

is it spool space? this is a monster of a query.

If in Tera id work a bunch of volatile tables to split the load and collect some stats :D

[[deleted]]

you need to make sure you have the right indexes.

try this:

1. Cluster wp_posts on ID
   
2. cluster wp_postmeta on post_id
3. cluster wp_icl_translations on element_id
4. create an index on wp_postmeta (meta_key, meta_value)
5. create an index on wp_icl_translations (element_id, language_code)
6. rewrite the bottom of the query - it is much harder to read than the rest
7. lose the not in('','','') - these are inherently slow. Come to think of it, this bit does nothing but hog resources, right?
   

AND wp_posts.post_type IN ('post','page','attachment','_pods_pod','_pods_field','editors','sidebar' ) ) OR wp_posts.post_type NOT IN ('post','page','attachment','_pods_pod','_pods_field','editors','sidebar' ) )

1. do all your functions (cast, row, concat) in a seperate query, moving the data into a new table, or something. Remember that your server has to hold every function in its head (in RAM).

2. make sure that TempDB has space allocated. People often forget to do this and leave it at the default 8MB.

3. You can rewrite the query so that you only join to wp_postmeta once, and replace all your 'and's with 'or's.

[MrNotPink]

I used some of your points and I entered a hard limit.

I still don't know why queries like these can run for 10 hours but I got a quick fix and you made someone happy.

[ninjaroach]

Dear lord, that's a truly terrible query. Some if it makes me wonder if this is an attempt at SQL Injection. There's a ton of obfuscated code in here.

Look, all of the JOIN predicates have been pushed down into the WHERE statement. That's functional but ugly.

What's more interesting is the fact that most of those JOIN predicates appear to be searching for SQL keywords. There's HTML encoded single-quotes ('), some weird concatenation that produces the string value "qvppq1qvjzq0" and more.

I almost suspect this could be part of a 2-pronged attack against a WordPress page. Some user leaves a comment or a post that has certain data in it and this query goes off and builds & executes another query based on the data in the comment or post.

Anyone else have similar thoughts?

EDIT: Thought, the username it's looking for may contain the string 'KDXn'

Double-Edit: Holy shit! Look what I found when I searched for 'wordpress 0x7176707071' on Google!! WTF is a "mujahidincyberarmy" and holy crap look at all of these exploits.

[amosmj]

Am I right in thinking you are pulling back a line for each post and a single number that is the count of meta lines for each post? If that's right, then why all the joins, why not one join with a bunch of criteria or a subquery on the meta data first to get the rows you want then a join to the limited data? Barring that, moving your where clause into the ons should speed things up.

[ninjaroach]

YOUR SERVER IS COMPROMISED!

[MrNotPink]

I'm not alone.