Need to understand why it's slow

Databases
1

Hi all ,

I have problem and I need help

I write this query …


SELECT ra.UserName, CONCAT( bd.site_id, '-', bd.sector ) AS site_sector
FROM radacct AS ra, bs_details AS bd
WHERE ra.UserName
IN (
	SELECT `user` 
	FROM crmcustomer
	WHERE `STATUS` = 'enabled'
)
AND bd.BS_MAC_address2 = SUBSTRING( ra.WiMaxBSId, 3 ) 
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

2

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.


[...]
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


      FROM radacct AS ra
INNER JOIN bs_details AS bd
        ON bd.BS_MAC_address2 = SUBSTRING( ra.WiMaxBSId, 3 )

Also,


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:


 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:


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
3

another possible optimization is to remove the GROUP BY clause

:slight_smile: