I decided to start optimizing queries on the online store, because there are already about 15,000 items in the product table, and the page load has become noticeably dimmed, not critical, but not very pleasant either. Here I want to consult with you, who, what will advise.

There are tables: 1 - t1 - collections 2 - t3 - goods (tied to collection by collection_id) 3 - t3 - characteristics of goods (tied to goods by product_id)

We display collections in the catalog, based on sorting by the characteristics of the goods, the query is cumbersome:

SELECT DISTINCT `t1`.`title`, `t1`.`id`, `t1`.`imgs`, `t1`.`price`, `t1`.`code_col`, `t1`.`brand_id`, count(*) as cnt FROM `t1` INNER JOIN `t2` ON `t1`.`id` = `t2`.`collection_id` INNER JOIN `t3` ON `t2`.`id` = `t3`.`product_id` WHERE `t1`.`section_id` = 1 + куча параметров из сортировки по разным таблицам GROUP BY `t2`.`id` HAVING cnt = 1 (кол-во разных параметров сортировки) ORDER BY `t1`.`price` * (1 - `sale`), `t1`.`id` LIMIT 0, 30 (и т.д. постраничная навигация по 30 штук на странице)

Sometimes there is another INNER JOIN with the sizes of the goods (a separate table of sizes, connects with the goods by product_id)

Is it possible to somehow ease this construction?

  • one
    The request itself is quite normal. Indexes will help you. - Dmitriy
  • In fact, the request is normal only syntactically (and even then - up to a certain version of MySQL), but in general it is more than strange. Yes, typical incomplete grouping. But if we take into account that fields from one table go in the output set, and the grouping is done by another field, the result becomes, to put it mildly, incomprehensible (especially since the group expression field is generally ignored during the output). This is not counting the simultaneous use of grouping and DISTINCT ... despite the fact that after grouping, only entries with count (*) = 1 are selected ... - Akina
  • With Explain it would be easier, of course, to advise something. I recommend enabling the strict mode (this will force the rewrite of the entire request) and get rid of the sort of t1 sorting . price` * (1 - sale ) - xoma
  • @Akina, and how to write requests for such a plan? with sorting by different product filters, which are in a separate database? - Vyacheslav Fedorov

0