Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New Coder
    Join Date
    Jan 2010
    Posts
    68
    Thanks
    3
    Thanked 5 Times in 4 Posts

    How to index this left join?

    What is the best way to index this left join?

    Or is there a better way to handle this query to make indexing easier?

    This query is being used to display comments for a page (postid).

    Each comment has it's own unique id (parent.id) and each child comment (reply) the parent.id is stored in (child.topid).

    PHP Code:
    SELECT parent.idparent.usernameparent.bodyparent.dtchild.idchild.usernamechild.bodychild.dt
    FROM comments 
    AS parent
    LEFT JOIN comments 
    AS child ON child.topid parent.id
    WHERE parent
    .postid 12 AND parent.topid AND parent.active 1
    ORDER BY parent
    .id DESCchild.id DESC 

  • #2
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,958
    Thanks
    120
    Thanked 76 Times in 76 Posts
    You don't index query. You put index onto table/s. Db inner optimizer then chooses which index to use, if any.

    If you want to see what it will chose use EXPLAIN before query.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Bubikol is of course correct.

    But for this simple case the answer is almost surely:
    -- comments.id is your primary key
    -- comments.topid is indexed as a NON-unique key.

    You *might* also want to index
    -- comments.postid

    Hard to tell, without knowing what the purposes of comments.id and comments.postid are.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    New to the CF scene
    Join Date
    Nov 2012
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    you can not index a query rather a table or some part of it atleast this is what happens in oracle

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Nancy: That is exactly what Bubikol said in post #2.

    Don't repeat other people's posts, please.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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