What are the cons of implementing faceted search with WHEREs and JOINs? 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.
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.