开发者

improve sql query performance

I have the following tables:

  • Products
  • Categories
  • Subcategories
  • Brands

with the following fields:

Products:

  • id
  • name
  • description1
  • description2
  • description3
  • id_category
  • id_subcategory
  • id_brand

Categories:

  • id
  • name

Subcategories

  • id
  • name

Brands

  • id
  • name

What I want is to make a query, where the user inputs 1 or more words and then search all the fields I have above.

This is my query now:

SELECT DISTINCT p. *
FROM products p, categories c, subcategories s, brands m
WHERE p.name LIKE 'word%'
OR p.description1 LIKE 'word%'
OR p.description2 LIKE 'word%'
OR p.description3 LIKE 'word%'
OR (
c.name LIKE 'word%'开发者_StackOverflow社区
AND c.id = p.id_category
)
OR (
s.name LIKE 'word%'
AND s.id = p.id_subcategory
)
OR (
m.name LIKE 'word%'
AND m.id = p.id_brand
)
LIMIT 10 ;

Is there any way to tweak this query to make it faster? The Database has quite a few products already, so I suspect the time it is taking for this query to show might be because of that..

Any sugestions?

Thank you!


Something like this should do the trick:

SELECT DISTINCT p. * 
FROM products p
LEFT JOIN categories c ON c.id = p.id_category
LEFT JOIN subcategories s ON s.id = p.id_subcategory
LEFT JOIN brands m ON m.id = p.id_brand
WHERE p.name LIKE 'word%' 
OR p.description1 LIKE 'word%' 
OR p.description2 LIKE 'word%' 
OR p.description3 LIKE 'word%' 
OR ( IFNULL(c.name, '') LIKE 'word%' ) 
OR ( IFNULL(s.name, '') LIKE 'word%' ) 
OR ( IFNULL(m.name, '') LIKE 'word%' ) 
LIMIT 10;


If full text search is not an option and assuming you have appropriately indexed you tables, you could try limiting the resultsets of the joined tables.

SELECT  DISTINCT p.* 
FROM    products p
        LEFT OUTER JOIN (
          SELECT  DISTINCT id
          FROM    categories 
          WHERE   name LIKE 'word%'
          LIMIT 10
        ) c ON c.id = p.id_category
        LEFT OUTER JOIN (
          SELECT  DISTINCT id
          FROM    subcategories 
          WHERE   name LIKE 'word%'
          LIMIT 10
        ) s ON s.id = p.id_subcategory 
        LEFT OUTER JOIN (
          SELECT  DISTINCT id
          FROM    brands 
          WHERE   name LIKE 'word%' 
          LIMIT 10
        ) m ON m.id = p.id_brand
WHERE   p.name LIKE 'word%' 
        OR p.description1 LIKE 'word%' 
        OR p.description2 LIKE 'word%' 
        OR p.description3 LIKE 'word%'         
LIMIT 10


Firstly, I would replace the conditional joining in the WHERE clause with JOIN

FROM products p INNER JOIN categories c ON c.id = p.id_category INNER JOIN subcategories s ON s.id = p.id_subcategory INNER JOIN brands m ON m.id = p.id_brand

(or use LEFT JOIN if you have NULLABLE FK's)

You then need to have indices on product(name), product(description1), product(description2), (description3), category(name), subcategory(name) and brand(name)

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜