Benderlio
Новичок
Есть такой запрос
EXPLAIN SELECT * , DATE_FORMAT( adv_date, "%e %M %Y" ) AS adv_date_out
FROM adv
JOIN category USING ( category_id )
JOIN city USING ( city_id )
JOIN region USING ( region_id )
JOIN img USING ( adv_id )
WHERE city_id = "542"
GROUP BY adv_id
ORDER BY adv_vip DESC , adv_date DESC
LIMIT 0 , 20
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE city const PRIMARY,reg_id PRIMARY 4 const 1 Using temporary; Using filesort
1 SIMPLE region const PRIMARY PRIMARY 4 const 1
1 SIMPLE img ALL adv_id NULL NULL NULL 11427
1 SIMPLE adv eq_ref PRIMARY,adv_category,city_id PRIMARY 4 guru_adv.img.adv_id 1 Using where
1 SIMPLE category eq_ref PRIMARY PRIMARY 4 guru_adv.adv.category_id 1
Убираю строку JOIN img USING ( adv_id ) и время возрастает до 1,5 ( было 0,15)
Вот эксплейн второго запроса
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE city const PRIMARY,reg_id PRIMARY 4 const 1 Using temporary; Using filesort
1 SIMPLE region const PRIMARY PRIMARY 4 const 1
1 SIMPLE adv ref adv_category,city_id city_id 4 const 34537 Using where
1 SIMPLE category eq_ref PRIMARY PRIMARY 4 guru_adv.adv.category_id 1
Почему при казалось бы уменьшении джойнов возрастает время исполнения запроса? Спасибо.
EXPLAIN SELECT * , DATE_FORMAT( adv_date, "%e %M %Y" ) AS adv_date_out
FROM adv
JOIN category USING ( category_id )
JOIN city USING ( city_id )
JOIN region USING ( region_id )
JOIN img USING ( adv_id )
WHERE city_id = "542"
GROUP BY adv_id
ORDER BY adv_vip DESC , adv_date DESC
LIMIT 0 , 20
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE city const PRIMARY,reg_id PRIMARY 4 const 1 Using temporary; Using filesort
1 SIMPLE region const PRIMARY PRIMARY 4 const 1
1 SIMPLE img ALL adv_id NULL NULL NULL 11427
1 SIMPLE adv eq_ref PRIMARY,adv_category,city_id PRIMARY 4 guru_adv.img.adv_id 1 Using where
1 SIMPLE category eq_ref PRIMARY PRIMARY 4 guru_adv.adv.category_id 1
Убираю строку JOIN img USING ( adv_id ) и время возрастает до 1,5 ( было 0,15)
Вот эксплейн второго запроса
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE city const PRIMARY,reg_id PRIMARY 4 const 1 Using temporary; Using filesort
1 SIMPLE region const PRIMARY PRIMARY 4 const 1
1 SIMPLE adv ref adv_category,city_id city_id 4 const 34537 Using where
1 SIMPLE category eq_ref PRIMARY PRIMARY 4 guru_adv.adv.category_id 1
Почему при казалось бы уменьшении джойнов возрастает время исполнения запроса? Спасибо.