5 added details from comments
source | link

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

SQL newbie here.

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

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.

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.

4 improved formatting; edited tags
source | link

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.

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.

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.

3 added 109 characters in body
source | link

I have this query:

SELECT * 
 FROM wp_dev_term_taxonomywp_dev_posts, wp_dev_termswp_dev_postmeta, 
 wp_dev_term_relationships WHERE ((wp_dev_term_taxonomy.taxonomypost_type = 'category')'projects') 
 OR (post_type = 'post_cost_codes')) AND (wp_dev_posts.ID = wp_dev_postmeta.post_id) AND (wp_dev_term_taxonomywp_dev_posts.term_idID = wp_dev_termswp_dev_term_relationships.term_idobject_id);

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

wp_dev_term_taxonomywp_dev_posts.term_idID = wp_dev_termswp_dev_term_relationships.term_idobject_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.

I have this query:

SELECT * 
 FROM wp_dev_term_taxonomy, wp_dev_terms 
 WHERE ((wp_dev_term_taxonomy.taxonomy = 'category')) 
       AND (wp_dev_term_taxonomy.term_id = wp_dev_terms.term_id);

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

wp_dev_term_taxonomy.term_id = wp_dev_terms.term_id

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

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.

2 added 24 characters in body
source | link
1
source | link