Optimization on table selected where primary key 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.
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.