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 ;

+----+--------------------+-------+--------+---------------------+------------+---------+------------------------------+------+-----------------------------------------------------------+
| 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;

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 
WHERE p.product_id in (
   SELECT p1.product_id 
     FROM product p1 
          LEFT JOIN product_to_value p2v1 ON ( p1.product_id = p2v1.product_id) 
          LEFT JOIN product_to_category p2c1 ON ( p1.product_id = p2c1.product_id) 
    WHERE p2c1.category_id = '125' 
      AND p2v1.value_id IN (29,31)
)
 AND p.product_id in (
   SELECT p2.product_id 
     FROM product p2 
          LEFT JOIN product_to_value p2v2 ON ( p2.product_id = p2v2.product_id) 
          LEFT JOIN product_to_category p2c2 ON ( p2.product_id = p2c2.product_id) 
    WHERE p2c2.category_id = '125' 
      AND p2v2.value_id IN (70,72)
)
 AND p.product_id in (
   SELECT p3.product_id 
     FROM product p3 
          LEFT JOIN product_to_value p2v3 ON ( p3.product_id = p2v3.product_id) 
          LEFT JOIN product_to_category p2c3 ON ( p3.product_id = p2c3.product_id) 
    WHERE p2c3.category_id = '125' 
      AND p2v3.value_id IN (74,75)
)
 AND p.product_id in (
   SELECT p4.product_id 
     FROM product p4 
          LEFT JOIN product_to_value p2v4 ON ( p4.product_id = p2v4.product_id) 
          LEFT JOIN product_to_category p2c4 ON ( p4.product_id = p2c4.product_id) 
    WHERE p2c4.category_id = '125' 
      AND p2v4.value_id IN (77,79,81)
)
 AND p.product_id in (
   SELECT p5.product_id 
     FROM product p5 
          LEFT JOIN product_to_value p2v5 ON ( p5.product_id = p2v5.product_id) 
          LEFT JOIN product_to_category p2c5 ON ( p5.product_id = p2c5.product_id) 
    WHERE p2c5.category_id = '125' 
      AND p2v5.value_id IN (86,499)
)
 AND p.product_id in (
   SELECT p6.product_id 
     FROM product p6 
          LEFT JOIN product_to_value p2v6 ON ( p6.product_id = p2v6.product_id) 
          LEFT JOIN product_to_category p2c6 ON ( p6.product_id = p2c6.product_id) 
    WHERE p2c6.category_id = '125' 
      AND p2v6.value_id IN (485,487)
)
 AND p.product_id in (
   SELECT p7.product_id 
     FROM product p7 
          LEFT JOIN product_to_value p2v7 ON ( p7.product_id = p2v7.product_id) 
          LEFT JOIN product_to_category p2c7 ON ( p7.product_id = p2c7.product_id) 
    WHERE p2c7.category_id = '125' 
      AND p2v7.value_id IN (97)
)
 AND p.product_id in (
   SELECT p8.product_id 
     FROM product p8 
          LEFT JOIN product_to_value p2v8 ON ( p8.product_id = p2v8.product_id) 
          LEFT JOIN product_to_category p2c8 ON ( p8.product_id = p2c8.product_id) 
    WHERE p2c8.category_id = '125' 
      AND p2v8.value_id IN (336,480)
)
 AND p.status = '1'
 AND p.date_available <= '2014-07-28 20:42:00'
 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 

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) 
WHERE p.product_id in (
   SELECT p1.product_id 
     FROM product p1 
          LEFT JOIN product_to_value p2v1 ON ( p1.product_id = p2v1.product_id) 
    WHERE p2v1.value_id IN (29,31)
)
 AND p.product_id in (
   SELECT p2.product_id 
     FROM product p2 
          LEFT JOIN product_to_value p2v2 ON ( p2.product_id = p2v2.product_id)           
    WHERE p2v2.value_id IN (70,72)
)
 AND p.product_id in (
   SELECT p3.product_id 
     FROM product p3 
          LEFT JOIN product_to_value p2v3 ON ( p3.product_id = p2v3.product_id) 
    WHERE p2v3.value_id IN (74,75)
)
 AND p.product_id in (
   SELECT p4.product_id 
     FROM product p4 
          LEFT JOIN product_to_value p2v4 ON ( p4.product_id = p2v4.product_id)           
    WHERE p2v4.value_id IN (77,79,81)
)
 AND p.product_id in (
   SELECT p5.product_id 
     FROM product p5 
          LEFT JOIN product_to_value p2v5 ON ( p5.product_id = p2v5.product_id)           
    WHERE p2v5.value_id IN (86,499)
)
 AND p.product_id in (
   SELECT p6.product_id 
     FROM product p6 
          LEFT JOIN product_to_value p2v6 ON ( p6.product_id = p2v6.product_id)           
    WHERE p2v6.value_id IN (485,487)
)
 AND p.product_id in (
   SELECT p7.product_id 
     FROM product p7 
          LEFT JOIN product_to_value p2v7 ON ( p7.product_id = p2v7.product_id)           
    WHERE p2v7.value_id IN (97)
)
 AND p.product_id in (
   SELECT p8.product_id 
     FROM product p8 
          LEFT JOIN product_to_value p2v8 ON ( p8.product_id = p2v8.product_id)           
    WHERE p2v8.value_id IN (336,480)
)
 AND p2c.category_id = '125' 
 AND p.status = '1'
 AND p.date_available <= '2014-07-28 20:42:00'
 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