MySQL very long-running queries

connectivityjoin;MySQLmysql-workbenchWordpress

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:

SELECT 
    COUNT(*)
FROM
    wp_posts
        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
WHERE
    wp_terms.term_id = 195;

Running over 600 seconds. Here is the wordpress db schema:
https://codex.wordpress.org/images/2/2a/WP3.9.4-ERD.png

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.