CODEWITHSUNDEEP
mysqlsql
Rohit Khurana
Rohit Khurana

Reputation: 269

How can I search records in a large table quickly?

I have a table(product) with 5.4 million recods. If i'm using below query to get result but it's working very slow. Is there a more efficient approach?

SELECT sd.imageid, sd.licencetype, sd.imgcollection, sd.orientation, 
       sd.pname, sd.pcaption, sd.ptype 
FROM   (SELECT imageid 
        FROM   product 
        WHERE  productkeyword IN (SELECT primary_kwd 
                                  FROM   searchkwdmgmt 
                                  WHERE  allkwd IN ( 'IPhone' )) 
        GROUP  BY imageid 
        HAVING Count(*) = 1 
        LIMIT  0, 31) q 
       JOIN searchdetails sd 
         ON sd.imageid = q.imageid

Upvotes: 0

Views: 137

Answers (3)

Raymond Nijland
Raymond Nijland

Reputation: 11602

This part is bad because of the subquery

SELECT imageid 
FROM product 
WHERE productkeyword IN (SELECT primary_kwd 
                         FROM searchkwdmgmt 
                         WHERE allkwd IN ('IPhone'))

Same query should return the same data but more efficient if the indexes are correctly set.

SELECT
    p.imageid 
FROM
    product as p
INNER JOIN 
    searchkwdmgmt as s ON p.productkeyword = s.primary_kwd 
WHERE 
    s.allkwd IN ('IPhone')

Test this first before you replace it...

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269693

Older versions of MySQL do a poor job optimizing in with subquery. If you know that there is at most one keyword match in searchkwdmgmt for each record in product you can do:

SELECT sd.imageid, sd.licencetype, sd.imgcollection, sd.orientation, 
       sd.pname, sd.pcaption, sd.ptype 
FROM   (SELECT p.imageid 
        FROM product p join
             searchkwdmgmt s
             on p.productkeyword = s.primary_kwd and
                s.allkwd in ( 'IPhone' )
        GROUP  BY p.imageid 
        HAVING Count(*) = 1 
        LIMIT  0, 31
       ) q join
       searchdetails sd 
       ON sd.imageid = q.imageid ;

If there can be more than one keyword match and there is an id in the product table, you can do:

SELECT sd.imageid, sd.licencetype, sd.imgcollection, sd.orientation, 
       sd.pname, sd.pcaption, sd.ptype 
FROM   (SELECT p.imageid 
        FROM product p join
             searchkwdmgmt s
             on p.productkeyword = s.primary_kwd and
                s.allkwd in ( 'IPhone' )
        GROUP  BY p.imageid 
        HAVING Count(distinct p.productid) = 1 
        LIMIT  0, 31
       ) q join
       searchdetails sd 
       ON sd.imageid = q.imageid ;

EDIT:

All of these versions will be faster with the following indexes: searchkwdmgmt(primary_kwd, allkwd), product(productkeyword, imageid), and searchdetails(imageid).

Upvotes: 1

Algorithmist
Algorithmist

Reputation: 6695

Create Indexes on below columns

1.Column:allkwd Table:searchkwdmgmt

2.Column:imageid Table:searchdetails

3.Column:imageid Table:product

4.Avoid Using In Clause

Upvotes: 1

Related Questions