Форум пользователей MySQL

Звучит конечно глуповато, но по сути полностью отражает ситуацию.

Вот сам запрос:


            SELECT p.product_id,
                   p.stock_status_id,
                   (SELECT AVG(rating) AS total FROM review r1 WHERE r1.product_id = p.product_id AND r1.status = '1') AS rating
              FROM product p
                   LEFT JOIN product_to_category p2c ON (p.product_id = p2c.product_id)
                   INNER JOIN product_to_value p2v1 ON (p.product_id = p2v1.product_id)
                   INNER JOIN product_to_value p2v2 ON (p.product_id = p2v2.product_id)
                   INNER JOIN product_to_value p2v3 ON (p.product_id = p2v3.product_id)
                   INNER JOIN product_to_value p2v4 ON (p.product_id = p2v4.product_id)
                   INNER JOIN product_to_value p2v5 ON (p.product_id = p2v5.product_id)
                   INNER JOIN product_to_value p2v6 ON (p.product_id = p2v6.product_id)
                   INNER JOIN product_to_value p2v7 ON (p.product_id = p2v7.product_id)
                   INNER JOIN product_to_value p2v8 ON (p.product_id = p2v8.product_id)
            WHERE p.status = '1'
              AND p.date_available <= '2014-07-28 20:42:00'
                    AND (p2c.category_id = '125')
          AND p2v1.value_id IN (29,31)
          AND p2v2.value_id IN (70,72)
          AND p2v3.value_id IN (74,75)
          AND p2v4.value_id IN (77,79,81)
          AND p2v5.value_id IN (86,499)
          AND p2v6.value_id IN (485,487)
          AND p2v7.value_id IN (97)
          AND p2v8.value_id IN (336,480)
            GROUP BY p.product_id
                    ORDER BY if(p.stock_status_id = 9, p.stock_status_id, NULL) ASC,
                             if(p.stock_status_id = 5, p.stock_status_id, NULL) ASC,
                             if(p.quantity = 0, p.quantity, NULL) ASC,
                             p.quantity ;

Выполняется он целых 500ms, что для меня долговато. Мне надо в пределах 5ms.

Статистика по таблицам:
* product - около 1,200 записей
* product_to_category - 1,500 записей
* review - около 2,000 записей
* product_to_value - 6,600 записей

Т.е. в общем и целом база небольшая, но почему так долго выполняется запрос - непонятно. Более того, если от него отрезать чуть-чуть, например последние два джойна с соответствующими условиями, то будет уже 21ms, что в 40 раз меньше. Аналогично, отрезав 3 условия, мы получаем уже 4ms вместо 500, что уже смахивает на приемлемый результат.

Я как бы понимаю что джойны это плохо, но другого варианта запроса я придумать не могу. Я увеличивал join_buffer_size до 8M - эффекта нет. Ставил и 16M - пофиг. Значит дело не в памяти. Но в чем тогда?

explain ничего интересного не говорит:


+----+--------------------+-------+--------+---------------------+------------+---------+------------------------------+------+-----------------------------------------------------------+
| id | select_type        | table | type   | possible_keys       | key        | key_len | ref                          | rows | Extra                                                     |
+----+--------------------+-------+--------+---------------------+------------+---------+------------------------------+------+-----------------------------------------------------------+
|  1 | PRIMARY            | p2v4  | range  | PRIMARY,vp,pv       | vp         | 4       | NULL                         |    9 | Using where; Using index; Using temporary; Using filesort |
|  1 | PRIMARY            | p2c   | eq_ref | PRIMARY,category_id | PRIMARY    | 8       | forter.p2v4.product_id,const |    1 | Using where; Using index                                  |
|  1 | PRIMARY            | p2v7  | ref    | PRIMARY,vp,pv       | PRIMARY    | 8       | forter.p2v4.product_id,const |    1 | Using index                                               |
|  1 | PRIMARY            | p2v1  | ref    | PRIMARY,vp,pv       | pv         | 4       | forter.p2c.product_id        |   10 | Using where; Using index                                  |
|  1 | PRIMARY            | p2v5  | ref    | PRIMARY,vp,pv       | pv         | 4       | forter.p2c.product_id        |   10 | Using where; Using index                                  |
|  1 | PRIMARY            | p2v6  | ref    | PRIMARY,vp,pv       | pv         | 4       | forter.p2v5.product_id       |   10 | Using where; Using index                                  |
|  1 | PRIMARY            | p2v3  | ref    | PRIMARY,vp,pv       | pv         | 4       | forter.p2v6.product_id       |   10 | Using where; Using index                                  |
|  1 | PRIMARY            | p2v2  | ref    | PRIMARY,vp,pv       | pv         | 4       | forter.p2v7.product_id       |   10 | Using where; Using index                                  |
|  1 | PRIMARY            | p2v8  | ref    | PRIMARY,vp,pv       | pv         | 4       | forter.p2v3.product_id       |   10 | Using where; Using index                                  |
|  1 | PRIMARY            | p     | eq_ref | PRIMARY,status      | PRIMARY    | 4       | forter.p2v6.product_id       |    1 | Using where                                               |
|  2 | DEPENDENT SUBQUERY | r1    | ref    | product_id          | product_id | 4       | func                         |    3 | Using where                                               |
+----+--------------------+-------+--------+---------------------+------------+---------+------------------------------+------+-----------------------------------------------------------+

А вот профайлинг говорит что запрос почти все свое время проторчал на статистике;


checking permissions 0.000003
checking permissions 0.000003
checking permissions 0.000003
checking permissions 0.000003
checking permissions 0.000003
checking permissions 0.000005
Opening tables 0.000051
System lock 0.000014
Waiting for query cache lock 0.000045
init 0.000088
optimizing 0.000029
statistics 0.487855  <<<<<< вот что это такое?
preparing 0.000072
Creating tmp table 0.000031
executing 0.000005
Copying to tmp table 0.000138
optimizing 0.000015
statistics 0.000018
preparing 0.000010
executing 0.000006
Sending data 0.000061
executing 0.000005
Sending data 0.000052
executing 0.000005
Sending data 0.000018
executing 0.000005
Sending data 0.000037
executing 0.000005
Sending data 0.000014
executing 0.000004
Sending data 0.000107
Sorting result 0.000020
Sending data 0.000016
end 0.000005
removing tmp table 0.000024
end 0.000007
query end 0.000005
closing tables 0.000014
freeing items 0.000017
Waiting for query cache lock 0.000005
freeing items 0.000021
Waiting for query cache lock 0.000004
freeing items 0.000005
storing result in query cache 0.000006
logging slow query 0.000004
cleaning up 0.000017


Ну и наконец таблички:


CREATE TABLE `product` (
  `product_id` int(11) NOT NULL AUTO_INCREMENT,
  `model` varchar(64) NOT NULL,
  `is_cheaper` int(1) NOT NULL DEFAULT '0',
  `sku` varchar(64) NOT NULL,
  `upc` varchar(12) NOT NULL,
  `location` varchar(128) NOT NULL,
  `quantity` int(4) NOT NULL DEFAULT '0',
  `stock_status_id` int(11) NOT NULL,
  `image` varchar(255) DEFAULT NULL,
  `manufacturer_id` int(11) NOT NULL,
  `shipping` tinyint(1) NOT NULL DEFAULT '1',
  `price` decimal(15,4) NOT NULL DEFAULT '0.0000',
  `super_price` tinyint(4) NOT NULL DEFAULT '0',
  `super_price_added` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `top_sale` tinyint(4) NOT NULL DEFAULT '0',
  `top_sale_end` date NOT NULL DEFAULT '0000-00-00',
  `payment_by_parts` tinyint(1) NOT NULL DEFAULT '0',
  `points` int(8) NOT NULL DEFAULT '0',
  `tax_class_id` int(11) NOT NULL,
  `date_available` date NOT NULL,
  `weight` decimal(15,8) NOT NULL DEFAULT '0.00000000',
  `weight_class_id` int(11) NOT NULL DEFAULT '0',
  `length` decimal(15,8) NOT NULL DEFAULT '0.00000000',
  `width` decimal(15,8) NOT NULL DEFAULT '0.00000000',
  `height` decimal(15,8) NOT NULL DEFAULT '0.00000000',
  `length_class_id` int(11) NOT NULL DEFAULT '0',
  `subtract` tinyint(1) NOT NULL DEFAULT '1',
  `minimum` int(11) NOT NULL DEFAULT '1',
  `sort_order` int(11) NOT NULL DEFAULT '0',
  `status` tinyint(1) NOT NULL DEFAULT '0',
  `date_added` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `date_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `viewed` int(5) NOT NULL DEFAULT '0',
  `installation` tinyint(1) NOT NULL DEFAULT '0',
  `price_uah` decimal(15,4) unsigned NOT NULL DEFAULT '0.0000',
  PRIMARY KEY (`product_id`),
  KEY `model` (`model`),
  KEY `quantity` (`quantity`),
  KEY `stock_status_id` (`stock_status_id`),
  KEY `manufacturer_id` (`manufacturer_id`),
  KEY `weight` (`weight`,`weight_class_id`),
  KEY `length` (`length`,`width`,`height`),
  KEY `height` (`height`,`length_class_id`),
  KEY `status` (`status`)
) ENGINE=MyISAM AUTO_INCREMENT=1397 DEFAULT CHARSET=utf8;

CREATE TABLE `review` (
  `review_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `customer_id` int(11) NOT NULL,
  `author` varchar(64) NOT NULL DEFAULT '',
  `text` text NOT NULL,
  `rating` int(1) NOT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '0',
  `date_added` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `date_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`review_id`),
  KEY `product_id` (`product_id`)
) ENGINE=MyISAM AUTO_INCREMENT=1957 DEFAULT CHARSET=utf8;

REATE TABLE `product_to_category` (
  `product_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
  `main_category` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`product_id`,`category_id`),
  KEY `category_id` (`category_id`),
  KEY `main_category` (`main_category`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `product_to_value` (
  `product_id` int(11) NOT NULL,
  `value_id` int(11) NOT NULL,
  `option_id` int(11) NOT NULL,
  PRIMARY KEY (`product_id`,`value_id`,`option_id`),
  KEY `vp` (`value_id`,`product_id`),
  KEY `pv` (`product_id`,`value_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;