3

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...

  • 3
    Run the statement again with EXPLAIN in front of the SELECT, i.e. EXPLAIN SELECT COUNT(*)... Post the result. – naschoff Nov 3 '15 at 15:28
  • 1
    Please provide SHOW CREATE TABLE for the 4 tables involved. The .png does not show the indexes. – Rick James Dec 4 '15 at 20:43
1

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.

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.