SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2011
    Posts
    44
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Wordpress query optimization

    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?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Henson4004 View Post
    I have to pick up the records from wp_posts table with in specifed category.
    you want INNER JOIN, not LEFT JOIN
    Code:
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2011
    Posts
    44
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •