2

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.

4
  • 1
    Get rid of "LEFT" in both places.
    – Rick James
    Jan 14, 2016 at 22:00
  • 7K rows should be adequately fast even if the query and indexes are inadequate. Are you having a performance problem? Will the table grow significantly? Please provide SHOW CREATE TABLE.
    – Rick James
    Jan 14, 2016 at 22:01
  • "Each one of this has its own table." -- Each what?
    – Rick James
    Jan 14, 2016 at 22:01
  • I edited the question. I'm not having a performance problem, but performance is something I would like to improve, so I'm interested in knowing if another approach would be much faster. The table will grow of course, but not that fast. Maybe 1000 rows per year.
    – Carlo
    Jan 15, 2016 at 10:14

2 Answers 2

1

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 the PRIMARY KEY and get rid if id?

Recommend you use standard 2-letter country_code instead of an extra table. And declare it CHAR(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.

1
  • The order is a tinyint, but it something that can change often, so I wouldn't use it in place of the id. I have a table for countries because it's connected also to other tables.
    – Carlo
    Jan 15, 2016 at 21:11
0

First of all, replace left join statement with inner join to speed up the query. Second, create indexes on every conjunction that you use in your query.

Also if a table isn’t being used in more than one relations, it is better to merge the table with the only table it is associated with.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.