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
inwp_terms
. It is probably thePRIMARY KEY
, which is good.