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 6 of 6
  1. #1
    Regular Coder
    Join Date
    Nov 2007
    Location
    Chicago
    Posts
    134
    Thanks
    2
    Thanked 9 Times in 9 Posts

    Query needs Optimization

    I'll start by saying that I am the furthest thing from a database admin... my knowledge is SQL queries is basic at best, which is why I'm asking for help here.

    I am attempting to build a "search" query on a client's message board (running vBulletin). The query I've written works fine - with the exception that it's terribly slow (if I set the start/limit params to 0 an 25, the query takes upwards of 30-60 seconds).

    Here's my query (a mix of PHP and raw SQL):

    PHP Code:
     $totalQuery '
                SELECT DISTINCT
                    thread.threadid,
                    thread.title,
                    thread.lastpost,
                    thread.lastposter,
                    thread.forumid,
                    thread.postusername,
                    thread.postuserid,
                    thread.dateline
                FROM thread
                RIGHT JOIN
                    (
                        SELECT
                            post.threadid,
                            post.pagetext
                        FROM post
                    ) AS post
                    ON thread.threadid = post.threadid
                WHERE thread.forumid NOT IN
                (
                    SELECT forum.forumid
                    FROM forum, forumpermission
                    WHERE FIND_IN_SET(forumpermission.forumid, forum.parentlist)
                    AND forumpermission.usergroupid >= "' 
    $_SESSION['perms']['permissions']->usergroupid '"
                )
                AND
                (
                    thread.title LIKE "%' 
    mysqli_real_escape_string($mysql$args['querystring']) . '%"
                    OR post.pagetext LIKE "%' 
    mysqli_real_escape_string($mysql$args['querystring']) . '%"
                )
                ORDER BY lastpost DESC
                LIMIT ' 
    $start ',' $limit
    So the idea is that I'm searching through both the post TITLE and post CONTENT for anything close to the provided query. However, I also need to make sure that the user only gets results that they have permissions to see... I imagine this probably causes the problem.

    There has to be a better way to optimize the query... all suggestions will be appreciated.

  • #2
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,963
    Thanks
    120
    Thanked 76 Times in 76 Posts
    This things are the same forinstance

    Code:
      RIGHT JOIN
                    (
                        SELECT
                            post.threadid,
                            post.pagetext
                        FROM post
                    ) AS post
                    ON thread.threadid = post.threadid
    Code:
       RIGHT JOIN post ON thread.threadid = post.threadid
    , the main thing then, that is slowing things down is, mybe you are not using indexes at all and use of '%.....%'

    to test my statement about like try to exclude

    Code:
     AND
                (
                    thread.title LIKE "%' . mysqli_real_escape_string($mysql, $args['querystring']) . '%"
                    OR post.pagetext LIKE "%' . mysqli_real_escape_string($mysql, $args['querystring']) . '%"
                )
    from sql and see the timing neded
    Last edited by BubikolRamios; 09-30-2011 at 09:29 PM.
    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
    Regular Coder
    Join Date
    Nov 2007
    Location
    Chicago
    Posts
    134
    Thanks
    2
    Thanked 9 Times in 9 Posts
    Ok, thanks for the response.

    That bit offers a slight improvement, so I'll make the change.

    However, I believe I need to do some sort of OUTER JOIN rather than the NOT IN statement... but I can't make sense of it. Any ideas?

  • #4
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,963
    Thanks
    120
    Thanked 76 Times in 76 Posts
    join forumpermission
    on thread.forumid = forumpermission.forumid and
    not FIND_IN_SET(forumpermission.forumid, forum.parentlist)
    AND forumpermission.usergroupid >= "' . $_SESSION['perms']['permissions']->usergroupid .


    having no tables struct, something close to that

    red part could go also into where part of sql then
    Last edited by BubikolRamios; 10-01-2011 at 06:15 AM.
    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

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,458
    Thanks
    76
    Thanked 4,373 Times in 4,338 Posts
    Your RIGHT JOIN has also been converted into an INNER JOIN whether you knew it or not.

    Look here:
    http://www.codingforums.com/showthre...192#post818192

    Since you are doing
    Code:
    FROM thread  RIGHT JOIN ...
    in this case thread is the dependent table and so any use of a WHERE clause on it means the RIGHT JOIN is transformed into an INNER JOIN.

    This may or may not matter for the results you want. You will have to decide.
    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.

  • #6
    Regular Coder
    Join Date
    Nov 2007
    Location
    Chicago
    Posts
    134
    Thanks
    2
    Thanked 9 Times in 9 Posts
    Thanks for the reply. The RIGHT JOIN (INNER JOIN) is fine, but I appreciate you pointing that out.

    I'm still working on the correct syntax for that JOIN statement... but I'm getting there. Hopefully by the end of today I'll have it figured out.

    What I've got so far:

    PHP Code:
                SELECT DISTINCT
                    thread
    .threadid,
                    
    thread.title,
                    
    thread.lastpost,
                    
    thread.lastposter,
                    
    thread.forumid,
                    
    thread.postusername,
                    
    thread.postuserid,
                    
    thread.dateline
                FROM thread
                INNER JOIN post
                    ON thread
    .threadid post.threadid

                INNER JOIN forum
                    ON thread
    .forumid forum.forumid

                RIGHT JOIN forumpermission
                    ON NOT FIND_IN_SET
    (forumpermission.forumidforum.parentlist)

                AND 
    forumpermission.usergroupid >= "0"

                
    WHERE 
                
                    thread
    .title LIKE "%test%"
                    
    OR post.pagetext LIKE "%test%"
                
        
    ORDER BY lastpost DESC
        LIMIT 0
    ,25
    Currently the JOIN on forumpermission doesn't return what I want it to, but I figure it may just be a matter of correcting the ON clause.


  •  

    Posting Permissions

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