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.