improve sql query performance

Question

Hello,

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%'
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!

Answer 1

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;

Answer 2

If full text search is not an option and assuming you have appropriatly 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

Answer 3

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)