SQL select query made MySQL server not responding and got no result

Question

I have two tables from target website in database and have to get post thumbnails rows. One table is wp_posts and another is wp_postmeta. Standard Wordpress export doesn't include featured images so I intended to get it myself. wp_posts contains all posts, and featured image also is a kind of post and stored in this table. wp_postmeta table contains different metadata linked with post id, and value of _thumbnail_id key is the ID of post type 'attachment'. The query below will clarify a little bit what I am trying to say.

Select * From wp_posts 
Where ID In (Select meta_value 
    From wp_postmeta 
    Where meta_key='_thumbnail_id' 
    And post_id In (Select ID From wp_posts 
        Where post_type='$post_type'));

And wp_post contains about 10k rows and wp_postmeta contains about 70k rows. Thanks for all kinda helping. Also will be great if you guys tell me how could I migrate specific post types with thumbnails from one wordpress installation to another.

s_ha_dum s_ha_dum · Accepted Answer · 2012-12-23T18:11:59

I think the nested IN clauses are likely the root of your performance problem. You need a moderately complicated query since WordPress 1) stores your post, which contains your post_type as one row in wp_posts, 2) the thumbnail 'attachment' as another row in wp_posts, and 3) the attachment metadata in wp_postmeta. I am sure that is why you where using the nested INs but INs are not especially efficient.

Your query does work, but with a database the size of yours you are probably running out of memory or time, or both. On my DB, which is much smaller, your query executes in 0.0123 seconds. The following query, which exactly duplicates your query's results, executes in 0.0006 seconds.

SELECT children.*
FROM wp_posts as children
JOIN wp_posts as parents on parents.ID = children.post_parent
WHERE children.ID IN (
    SELECT meta_value
    FROM wp_postmeta
    WHERE post_id = parents.ID
    AND meta_key='_thumbnail_id'
) 
AND parents.post_type='{$post_type}'

Given that it returns 4 rows from my DB, you can see what a huge execution difference that is.

The following query is not much slower than the one above (0.0013 seconds) but returns rows not returned by the previous query (one extra in my case), and is still many times faster than yours. I think it is the query you want.

SELECT *
FROM wp_posts as children
JOIN wp_posts as parents on parents.ID = children.post_parent
JOIN wp_postmeta ON parents.ID = wp_postmeta.post_id
AND wp_postmeta.meta_key = '_thumbnail_id'
AND children.post_type = 'attachment'
AND parents.post_type = '{$post_type}'

SQL select query made MySQL server not responding and got no result

1 Answers