PRO
Новичок
Оптимизация запроса. EXPLAIN прилагается.
Запрос:
SELECT lots . * , users.login, (
SELECT CAST( GROUP_CONCAT( images.img_id, '_', images.server_id, '_', images.ext, '_', images.img_date
SEPARATOR ';' ) AS CHAR )
FROM images
WHERE images.lot_id = lots.lot_id
AND images.img_active =1
GROUP BY images.lot_id
) AS images_str, users_bid . * , count( users_bid.lot_id )
FROM lots
LEFT JOIN users ON users.user_id = lots.owner
LEFT JOIN (
SELECT bids . * , u2.login AS lider_login
FROM bids
LEFT JOIN users u2 ON u2.user_id = bids.user_id
WHERE bids.b_see =1
ORDER BY summ DESC , b_type
) AS users_bid ON users_bid.lot_id = lots.lot_id
INNER JOIN nominal ON nominal.nominal_id = lots.nominal_id
LEFT JOIN category ON category.category_id = lots.category_id
WHERE lots.ltype =0
AND lots.auk_id =14
GROUP BY lots.lot_id
ORDER BY category.weight, nominal.weight
EXPLAIN :
Смущают строки с таблицами bids и <derived3>.
Может кто поможет советом в плане оптимизации.
Заранее спасибо.
Запрос:
SELECT lots . * , users.login, (
SELECT CAST( GROUP_CONCAT( images.img_id, '_', images.server_id, '_', images.ext, '_', images.img_date
SEPARATOR ';' ) AS CHAR )
FROM images
WHERE images.lot_id = lots.lot_id
AND images.img_active =1
GROUP BY images.lot_id
) AS images_str, users_bid . * , count( users_bid.lot_id )
FROM lots
LEFT JOIN users ON users.user_id = lots.owner
LEFT JOIN (
SELECT bids . * , u2.login AS lider_login
FROM bids
LEFT JOIN users u2 ON u2.user_id = bids.user_id
WHERE bids.b_see =1
ORDER BY summ DESC , b_type
) AS users_bid ON users_bid.lot_id = lots.lot_id
INNER JOIN nominal ON nominal.nominal_id = lots.nominal_id
LEFT JOIN category ON category.category_id = lots.category_id
WHERE lots.ltype =0
AND lots.auk_id =14
GROUP BY lots.lot_id
ORDER BY category.weight, nominal.weight
EXPLAIN :
Код:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY lots ref nominal_id,auk_id_2 auk_id_2 6 const,const 3 Using temporary; Using filesort
1 PRIMARY users eq_ref PRIMARY PRIMARY 8 aucoins.lots.owner 1
1 PRIMARY <derived3> ALL NULL NULL NULL NULL 8
1 PRIMARY category eq_ref PRIMARY PRIMARY 2 aucoins.lots.category_id 1
1 PRIMARY nominal eq_ref PRIMARY PRIMARY 2 aucoins.lots.nominal_id 1 Using where
3 DERIVED bids ALL b_see b_see 1 7 Using filesort
3 DERIVED u2 eq_ref PRIMARY PRIMARY 8 aucoins.bids.user_id 1
2 DEPENDENT SUBQUERY images ref lot_id_2 lot_id_2 10 func,const 2 Using whereМожет кто поможет советом в плане оптимизации.
Заранее спасибо.
