Hi,
I am working in wordpress database. I have to pick up the records from wp_posts table with in specifed category. Categories are saved under wp_term_relationships table having field name: term_taxonomy_id so i am using left join as:
explain SELECT wp_posts.*, wp_term_relationships.term_taxonomy_id FROM wp_posts LEFT JOIN wp_term_relationships ON (wp_posts.ID=wp_term_relationships.object_id) WHERE wp_posts.post_type=‘post’ and wp_posts.post_status=‘publish’ and (wp_term_relationships.term_taxonomy_id=8 or wp_term_relationships.term_taxonomy_id=1) ORDER BY wp_posts.post_date desc LIMIT 0, 2
Explain results are as under:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE wp_posts ref PRIMARY,type_status_date,ptsn type_status_date 124 const,const 105 Using where
1 SIMPLE wp_term_relationships ref PRIMARY,term_taxonomy_id PRIMARY 8 p2p_blogs.wp_posts.ID 1 Using where; Using index
I am afraid if i am getting right number of rows or can be further optimized?
SELECT wp_posts.*
, wp_term_relationships.term_taxonomy_id
FROM wp_term_relationships
INNER
JOIN wp_posts
ON wp_posts.ID = wp_term_relationships.object_id
AND wp_posts.post_type = 'post'
AND wp_posts.post_status = 'publish'
WHERE wp_term_relationships.term_taxonomy_id IN ( 1,8 )
ORDER
BY wp_posts.post_date DESC LIMIT 0, 2
Getting same result against explain inner join query:
EXPLAIN SELECT wp_posts . * , wp_term_relationships.term_taxonomy_id
FROM wp_term_relationships
INNER
JOIN wp_posts ON wp_posts.ID = wp_term_relationships.object_id
AND wp_posts.post_type = ‘post’
AND wp_posts.post_status = ‘publish’
WHERE wp_term_relationships.term_taxonomy_id
IN ( 1, 8 )
ORDER
BY wp_posts.post_date DESC
LIMIT 0 , 2
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE wp_posts ref PRIMARY,type_status_date,ptsn type_status_date 124 const,const 105 Using where
1 SIMPLE wp_term_relationships ref PRIMARY,term_taxonomy_id PRIMARY 8 p2p_blogs.wp_posts.ID 1 Using where; Using index