Доброго дня.
Есть довольно тяжелый запрос который выполняется несколько секунд. Хотелось бы его оптимизировать.
Про EXPLAIN знаю, но вывод пока для меня не очевиден.
explain SELECT COUNT(*) FROM (SELECT `t`.`ART_ID` AS `t0_c0`, `t`.`ART_ARTICLE_NR` AS `t0_c1`, `t`.`ART_SUP_ID` AS `t0_c2`, `t`.`ART_DES_ID` AS `t0_c3`, `t`.`ART_COMPLETE_DES_ID` AS `t0_c4`, `t`.`ART_PACK_SELFSERVICE` AS `t0_c5`, `t`.`ART_MATERIAL_MARK` AS `t0_c6`, `t`.`ART_REPLACEMENT` AS `t0_c7`, `t`.`ART_ACCESSORY` AS `t0_c8`, `t`.`ART_BATCH_SIZE1` AS `t0_c9`, `t`.`ART_BATCH_SIZE2` AS `t0_c10`, `_tmp2`.`LAT_STR_ID` AS `t1_c0`, `_tmp2`.`LAT_ART_ID` AS `t1_c1`, `scores`.`id` AS `t2_c0`, `scores`.`ART_ID` AS `t2_c1`, `scores`.`scores` AS `t2_c2`, `minmaxrate`.`id` AS `t3_c0`, `minmaxrate`.`art_id` AS `t3_c1`, `minmaxrate`.`min_rate` AS `t3_c2`, `minmaxrate`.`max_rate` AS `t3_c3`, `minmaxrate`.`city_id` AS `t3_c4` FROM `tecdoc_4q_2014`.`ARTICLES` `t` LEFT OUTER JOIN `LINK_ARTICLES_TREE` `_tmp2` ON (`_tmp2`.`LAT_ART_ID`=`t`.`ART_ID`) LEFT OUTER JOIN `autocat`.`article_scores` `scores` ON (`scores`.`ART_ID`=`t`.`ART_ID`) LEFT OUTER JOIN `autocat`.`article_rates` `minmaxrate` ON (`minmaxrate`.`art_id`=`t`.`ART_ID`) WHERE (((LAT_STR_ID=10126) AND (minmaxrate.city_id=4429)) AND (min_rate > 0 or max_rate > 0)) GROUP BY ART_ARTICLE_NR) as sq;
+----+-------------+------------+--------+--------------------+--------------------+---------+---------------------------------+---------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+--------------------+--------------------+---------+---------------------------------+---------+-----------------------------------------------------------+
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
| 2 | DERIVED | _tmp2 | ref | index1,index2 | index1 | 4 | | 1168434 | Using where; Using index; Using temporary; Using filesort |
| 2 | DERIVED | t | eq_ref | PRIMARY | PRIMARY | 4 | tecdoc_4q_2014._tmp2.LAT_ART_ID | 1 | Using where |
| 2 | DERIVED | minmaxrate | ref | article_rates_idx1 | article_rates_idx1 | 4 | tecdoc_4q_2014._tmp2.LAT_ART_ID | 1 | Using where |
| 2 | DERIVED | scores | ALL | NULL | NULL | NULL | NULL | 4 | |
+----+-------------+------------+--------+--------------------+--------------------+---------+---------------------------------+---------+-----------------------------------------------------------+
5 rows in set (11.19 sec)
Про индексы я знаю, но увы пользоваться EXPLAINом с выводами для себя пока не умею.
Хотелось бы чтоб уважаемый ALL сказал узкие места и комментарии по колонкам - как я понимаю здесь показывается основная информация по использованию индексов.
Средний