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)
精彩评论