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.
SHOW CREATE TABLE
.