Suneth Kalhara Suneth Kalhara - 3 months ago 21
MySQL Question

MySQL select query need some optimization

I'm trying to build a query for search page this my first query, and this is very fast the results load within Milli seconds

$query is php variable which is search text

$sql="SELECT * FROM
tbl_product
WHERE
((tbl_product.pd_name LIKE '%$query%')
OR (tbl_product.pd_art LIKE '%$query%')
OR (tbl_product.pd_srkw LIKE '%$query%'))
AND pd_bestsell <> 1
ORDER BY RAND()";


But when i build a query with search suggestions table it's very slow and take 2-4 seconds for load. our server is very powerful dedicated one so it cannot be a issue with server.

sgtexts= keep suggestion texts

sgproduct =keep products ids on suggestion table

$sql="SELECT * FROM
tbl_product,tbl_suggetions
WHERE
((tbl_product.pd_name LIKE '%$query%')
OR (tbl_product.pd_art LIKE '%$query%')
OR (tbl_product.pd_srkw LIKE '%$query%')
OR (tbl_suggetions.sgtexts LIKE '%$query%'
AND tbl_product.pd_id=tbl_suggetions.sgproduct))
AND pd_bestsell <> 1
GROUP BY pd_id
ORDER BY RAND()";


can anyone help me for optimize second query please

Answer

Is there any reason why you order by random? Removing this will make it faster.

Since you are using LIKE operator with %%, this will perform a full table scan and is by nature slower than using LIKE 'example%'.

Also, use a join on your table. Try this

SELECT 
        * 
FROM    tbl_product         tbl_product
JOIN    tbl_suggetions      tbl_suggetions ON tbl_suggetions.sgproduct=tbl_product.pd_id
WHERE 
(
(tbl_product.pd_name    LIKE '%$query%')        OR 
(tbl_product.pd_art     LIKE '%$query%')        OR 
(tbl_product.pd_srkw    LIKE '%$query%')        OR 
(tbl_suggetions.sgtexts LIKE '%$query%')
)  
    AND pd_bestsell <> 1 
    GROUP BY pd_id 
    ORDER BY RAND();

If the data doesnt need to be randomly ordered, remove the ORDER BY RAND()