MySQL select query need some optimization

Question

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

EXPLAIN should give you the answer. dev.mysql.com/doc/refman/5.7/en/explain.html — Jakub Matczak
– Jakub Matczak, Commented Aug 30, 2016 at 8:38
Is tbl_suggetions.sgtexts indexed? And isn't suggetions a typo? — Ben Hillier
– Ben Hillier, Commented Aug 30, 2016 at 8:38

Mad Dog Tannen · Accepted Answer · 2016-08-30 08:40:55Z

3

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()

answered Aug 30, 2016 at 8:40

Mad Dog Tannen

7,2505 gold badges34 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Jakub Matczak Over a year ago

You're query has exactly the same efficiency, just has different syntax.

Mad Dog Tannen Over a year ago

@SunethKalhara , also make sure you only in the SELECT return the columns that you actually need. Now it will return all columns from both tables.

Suneth Kalhara Over a year ago

@KayNelson the query is very fast my query took 15.3766 sec but this took 0.0728 sec, but i got a problem my query return 71 results but new one only return 47 results, any idea why is that, Thanks

Mad Dog Tannen Over a year ago

It most likley has to do with using OR & AND in the WHERE query.

Mad Dog Tannen Over a year ago

@SunethKalhara I think you can also speed iup your query by using pd_bestsell != 1, this will get all pd_bestsell where its not 1. what your are using now could give back where pd_bestsell=0 for instance, is that a possibility?

|

Collectives™ on Stack Overflow

MySQL select query need some optimization

1 Answer 1

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related