How to fast return and count rows from one table based on filter by two another tables

Question

I have 3 tables:

products (1 500 000 rows)

+------------+-------------+-------------+-------------+
|     id     |     shop    |    brand    |   category  |
+------------+-------------+-------------+-------------+

id int(11), AI, PK;
shop int(11);
brand int(11);
category int(11);

tags (1 300 000 rows)

+------------+------------+-------------+
|     id     |   product  |     tag     |
+------------+------------+-------------+

id int(11), AI, PK;
product int(11) - it's id from 'products' table;
tag int(11);

sizes (1 300 000 rows)

+------------+------------+-------------+
|     id     |   product  |     size    |
+------------+------------+-------------+

id int(11), AI, PK;
product int(11) - it's id from 'products' table;
tag int(11);

Every column has index.

I need to filter all products (including count rows) which may be filtered by brand,shop,category,tag,size. So, for example if i need to just show products where brand=1 and shop=1 I will not use tags and sizes tables. It's simple. But the problem is, when I would like to show all products which are in some categories, some shops, has some brand, some tags and some size.

There are many combinations to cache all of them and update. Get live count costs about 6 second. To show all filtered products is fast because of limit for paggination.

I have spent more then week to test what is faster, which option and structure of database is better but definitely don't know to make it faster than this:

select p.id
  FROM
products p
  INNER JOIN tags t ON(p.id = t.product and tags=2) 
  INNER JOIN sizes s ON(p.id = s.product and sizes in (1,2))
  WHERE
p.shop in (1,13,31,65)
  and 
p.category in (270,126,127,144,143,145,146,839,147,148,149,150,158,151,155,157,123,124,128,129,602,120,121,122,152,153,154,482,526,40,42,46,115,119,138,142,133,135,136,137,130,131,132,116,117,32,103,485,112,113,114,566,39,107,108,109,110,118,265,516,527,528,529,530,106,159,161,185,30,86,87,88,89,90,91,92,267,531,532,104,105,28,29,31,33,34,35,36,37,38,41,43,44,45,102,165,269,487,2)
  GROUP BY p.id

There are now only 1,5M products and the filtering based on category which has 200k products (because of top of tree) is loading (counting and showing data) about 5s. It's because filtered products has result of 13k rows in category of 200k products. But still need to compare and search in all data tables. One of solution could be to separate all products to f.e. 10 table categories (for clothing, electro, drug store, food...) and search just there. But I don't think it's the best solution for the problem. Only better then now.

Doing result of this SQL with the order by p.id DESC limit 0,120 is pretty fast. About 0,8s but count them in some case is about 8-12s. Is there any chance to get faster the result and count by changing SQL query or changing structure of DB?

Thank you for any help.

user_12230544 · Answer

Try to make separate caches for all types of filtering. For example:

$tagProductIds = $yourCachingSystem->getAllIdsByTag($_POST['tag']);
$sizeProductIds = $yourCachingSystem->getAllIdsBySize($_POST['size']); 
$categoryProductIds = $yourCachingSystem->getAllIdsByCategory($_POST['category']); 

//...other filters by the product table

$neededProductIds = array_intersect($tagProductIds, $sizeProductIds, $categoryProductIds
//...other filtered ids
);

return $yourOrm->fetchAll(" ... product.id IN ($neededProductIds)");

You can create MySQL tables using the MEMORY engine. These tables are stored in the RAM and maybe this will speed up the extraction of data.

CREATE TABLE if not exists  `products_tmp`
(
   //cols and indexes
) ENGINE = MEMORY
SELECT * products; //tags, sizes

How to fast return and count rows from one table based on filter by two another tables

1 Answers