I'm implementing a filter search
system (I understood today that is called faceted search
) for an ecommerce website.
The fron end looks something like this:
Country:
[Select a country]
Categories:
(x) category 1
( ) category 2
(x) category 3
...
Format:
( ) format 1
(x) format 2
( ) format 3
...
...
I'm implementing it with an AND
between each filters.
I implemented it my way, because I didn't know it was called faceted search and couldn't find suggestions on how to do it, so I came up with something like this:
SELECT ...
FROM (products AS p)
LEFT JOIN countries AS c ON c.id=p.country_id
JOIN categories_products AS cp ON p.id=cp.product_id
JOIN categories AS cat ON cp.category_id=cat.id
LEFT JOIN formats AS f ON f.id=p.format_id
WHERE cat.id IN (9, 12)
AND f.id IN (3, 5, 6, 7)
AND c.id = 94
GROUP BY p.id
HAVING COUNT(DISTINCT(cat.id)) = 2;
Basically, a product has only one country, but can belong to any number of categories. Then there's the format and other filters which I'm not displaying here. Each one of this has its own table.
It's working the way it is, but today I read about implementations with a facet table where are stored facts
. So, from what I understood, every row would represent every possible combination of options.
I'm filtering a table of about 7000 rows. Is my approach (that) bad from a perspective point view? Other cons?
CREATE TABLE `products` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`country_id` int(11) DEFAULT NULL,
`format_id` int(11) DEFAULT NULL,
`featured` tinyint(1) DEFAULT '0',
`is_limited` tinyint(1) DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `order` (`order`),
KEY `photographer` (`user_id`)
) ENGINE=MyISAM AUTO_INCREMENT=33514 DEFAULT CHARSET=utf8;
This is the relevant part of the table. I omitted some fields like name, description and some others.
What I meant saying that "each one of these has its own table" was that each of these filters (categories, format, country) has its own table. So there's a categories
table and a categories_products
relation table; there's a countries
table; a formats
table and so on.
Best Answer
You should have indexes on more of the fields; it gets tedious scanning all 7K rows.
What is the datatype of
order
; can it be thePRIMARY KEY
and get rid ifid
?Recommend you use standard 2-letter
country_code
instead of an extra table. And declare itCHAR(2) CHARACTER SET ascii
.If you expect to have a million rows, I would say that you will have lots of performance problems. But... 1K/minute is exciting; 1K/second is challenging; 1K/year is nothing. -- So, performance is not an issue.