SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Addict ruba's Avatar
    Join Date
    Apr 2005
    Location
    Amman -Jordan
    Posts
    339
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    need to understand why it's slow

    Hi all ,

    I have problem and I need help

    I write this query ..

    PHP Code:
    SELECT ra.UserNameCONCATbd.site_id'-'bd.sector ) AS site_sector
    FROM radacct 
    AS rabs_details AS bd
    WHERE ra
    .UserName
    IN 
    (
        
    SELECT `user
        
    FROM crmcustomer
        WHERE 
    `STATUS` = 'enabled'
    )
    AND 
    bd.BS_MAC_address2 SUBSTRINGra.WiMaxBSId
    GROUP BY ra.UserName 

    it take time more than 30sec and return 145 record only.

    but when I add limit 1000 it take less than one sec.

    I don't understand why ??

    any body can help me to understand ? or how to optimize query


    please advice
    thanks
    Open Blocked website
    Open Blocked Website
    Knowledge Is Knowing That A Tomato Is A Fruit,
    Wisdom Is Not Putting It In A Fruit Salad.

  2. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,097
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    LIMIT 1000 in itself should not speed up the query. The only thing LIMIT does is limit the number of results that are returned from the final result set; all calculations before that are exactly the same as without the LIMIT.
    My guess is that it runs faster in consecutive queries because the result is stored in query cache.

    That being said, you're query could definitely be optimized.

    Code:
    [...]
    FROM radacct AS ra, bs_details AS bd
    [...]
    WHERE
    [...]
    AND bd.BS_MAC_address2 = SUBSTRING( ra.WiMaxBSId, 3 )
    This creates the carthesian product of ra and bd (matching up every row in ra with every row in bd) which are known to be extremely slow.

    A better solution would be
    Code:
          FROM radacct AS ra
    INNER JOIN bs_details AS bd
            ON bd.BS_MAC_address2 = SUBSTRING( ra.WiMaxBSId, 3 )
    Also,
    Code:
    WHERE ra.UserName
    IN (
        SELECT `user` 
        FROM crmcustomer
        WHERE `STATUS` = 'enabled'
    )
    is quite slow as well, and can also be rewritten to a JOIN, which is way more efficient. To add it to the previous join:

    Code:
     FROM radacct AS ra
    INNER JOIN bs_details AS bd
            ON bd.BS_MAC_address2 = SUBSTRING( ra.WiMaxBSId, 3 ) 
    INNER JOIN crmcustomer c
            ON c.`user`=ra.userName and c.`status`='enabled'
    So, all in all the query would be:

    Code:
    SELECT ra.UserName, CONCAT( bd.site_id, '-', bd.sector ) AS site_sector
    FROM radacct AS ra
    INNER JOIN bs_details AS bd
            ON bd.BS_MAC_address2 = SUBSTRING( ra.WiMaxBSId, 3 ) 
    INNER JOIN crmcustomer c
            ON c.`user`=ra.userName and c.`status`='enabled'
    GROUP BY ra.UserName
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    another possible optimization is to remove the GROUP BY clause

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •