MySQL very long-running queries


I have a quite large db (800+Mb dump file) that I imported to my local server. It's a WordPress db from witch I need to extract certain posts. There are around 160,000 posts inside.

Currently I'm testing a bit with MySQL Workbench running simple queries with JOIN and it requires a lot of time, so long actually that Workbench stops processing.

Here's an example:

        LEFT JOIN
    wp_term_relationships ON wp_posts.ID = wp_term_relationships.object_id
        LEFT JOIN
    wp_term_taxonomy ON wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
        LEFT JOIN
    wp_terms ON wp_term_taxonomy.term_id = wp_terms.term_id
    wp_terms.term_id = 195;

Running over 600 seconds. Here is the wordpress db schema:

Of course chance is I'm just bad at SQL, not really my field…

Best Answer

Remove all the LEFTs.

Make sure there is an index on term_id in wp_terms. It is probably the PRIMARY KEY, which is good.