1

I have this query:

SELECT * 
FROM wp_dev_posts, wp_dev_postmeta, wp_dev_term_relationships 
WHERE ((post_type = 'projects') OR (post_type = 'post_cost_codes')) 
    AND (wp_dev_posts.ID = wp_dev_postmeta.post_id) 
    AND (wp_dev_posts.ID = wp_dev_term_relationships.object_id);

My question, is that when I have this in the where:

wp_dev_posts.ID = wp_dev_term_relationships.object_id

What's the correct way to optimize this? Am I better to use joins? Do I set index's? Do I use foreign keys? On a large database this is actually take a few seconds to run.

SQL newbie here.

FYI, this is Wordpress, with 33k rows in posts, about 250k rows in postmeta.

1

Generally speaking, you'd want to rewrite that query like this:

SELECT * 
FROM wp_dev_posts
    INNER JOIN wp_dev_postmeta 
        ON wp_dev_posts.ID = wp_dev_postmeta.post_id
    INNER JOIN wp_dev_term_relationships 
        ON wp_dev_posts.ID = wp_dev_term_relationships.object_id
WHERE ((post_type = 'projects') OR (post_type = 'post_cost_codes'));

Notice, I've moved parts of the WHERE clause to the FROM clause, using the ON construct. That makes the query easier to read.

To optimize access to the table, you'd generally like to have indexes on wp_dev_posts.ID, wp_dev_postmeta.post_id, wp_dev_term_relationships.object_id, and post_type.

It might make sense to create a compound index on wp_dev_posts for (post_type, ID).

You'd probably want a foreign key on wp_dev_postmeta pointing to the primary key defined on wp_dev_posts.ID.

Providing more details about your specific environment, including the definition of the tables, any existing queries, row counts, etc, would help us provide a more definitive answer.

  • 1
    Thanks, I tried this out in my code, but the speed was still slow. I put it directly into phpymadmin and it's blazing fast. Looks like I have to figure out why wordpress specifically makes my query super slow. You answered the question as asked, so I marked your answer. Thanks for the time Max – Justin J James Aug 2 at 16:41
  • my pleasure. I'm not sure if you're aware, but we have a sister site specifically for wordpress at wordpress.stackexchange.com – Max Vernon Aug 2 at 16:58
  • posted, and using your re-written query. If you have any thoughts feel free to post – Justin J James Aug 2 at 18:40
  • I upvoted your question there, but I have no real ideas as to why wordpress is taking so much longer. – Max Vernon Aug 2 at 19:19

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.