-
Юбилейный DevConfX пройдет 21-22 июня в Москве. Как всегда — Вы решаете, кто попадет в программу секции Backend — голосуйте за интересные доклады
Оптимизация запроса
- Автор темы LONGMAN
- Дата начала
LONGMAN
Dark Side of the Moon..
Wicked
конечно.
Запрос:
[sql]
SELECT `p` . *
FROM `shoppingcart_products` AS `p`
INNER JOIN (
SELECT `id`
FROM `shoppingcart_products`
ORDER BY `date` DESC
LIMIT 10
) AS `p2` ON ( `p`.`id` = `p2`.`id` )
LEFT JOIN `shoppingcart_products_data` AS `d` ON `p`.`id` = `d`.`itemid`
LEFT JOIN `shoppingcart_products_categories` AS `pc` ON `pc`.`pid` = `p`.`id`
LEFT JOIN `shoppingcart_categories` AS `cc` ON `cc`.`id` = `pc`.`cid`
LEFT JOIN `shoppingcart_currency` AS `cur` ON `cur`.`id` = `p`.`currency`
LEFT JOIN `shoppingcart_manufacturers_data` AS `m` ON `m`.`itemid` = `p`.`manufacturer`
LEFT JOIN `shoppingcart_users` AS `u` ON `u`.`id` = `p`.`added_by`
GROUP BY `p`.`id`
[/sql]
Explain:
-~{}~ 05.08.10 12:28:
Не смотря на опять печальный explain, запрос выполняется гораздо быстрее.
Первый вариант: 1.3057 sec
Второй вариант: 0.0108 sec
Обе выполнил с ключом SQL_NO_CACHE
конечно.
Запрос:
[sql]
SELECT `p` . *
FROM `shoppingcart_products` AS `p`
INNER JOIN (
SELECT `id`
FROM `shoppingcart_products`
ORDER BY `date` DESC
LIMIT 10
) AS `p2` ON ( `p`.`id` = `p2`.`id` )
LEFT JOIN `shoppingcart_products_data` AS `d` ON `p`.`id` = `d`.`itemid`
LEFT JOIN `shoppingcart_products_categories` AS `pc` ON `pc`.`pid` = `p`.`id`
LEFT JOIN `shoppingcart_categories` AS `cc` ON `cc`.`id` = `pc`.`cid`
LEFT JOIN `shoppingcart_currency` AS `cur` ON `cur`.`id` = `p`.`currency`
LEFT JOIN `shoppingcart_manufacturers_data` AS `m` ON `m`.`itemid` = `p`.`manufacturer`
LEFT JOIN `shoppingcart_users` AS `u` ON `u`.`id` = `p`.`added_by`
GROUP BY `p`.`id`
[/sql]
Explain:
Код:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 10 Using temporary; Using filesort
1 PRIMARY p eq_ref PRIMARY PRIMARY 4 p2.id 1
1 PRIMARY d ref itemid itemid 4 mydb.p.id 2 Using index
1 PRIMARY pc ref PRIMARY PRIMARY 4 mydb.p.id 2 Using index
1 PRIMARY cc eq_ref PRIMARY PRIMARY 4 mydb.pc.cid 1 Using index
1 PRIMARY cur eq_ref PRIMARY PRIMARY 4 mydb.p.currency 1 Using index
1 PRIMARY m ref itemid itemid 4 mydb.p.manufacturer 1 Using index
1 PRIMARY u eq_ref PRIMARY PRIMARY 4 mydb.p.added_by 1 Using index
2 DERIVED elva_wsshoppingcart_products index NULL date 8 NULL 4655Не смотря на опять печальный explain, запрос выполняется гораздо быстрее.
Первый вариант: 1.3057 sec
Второй вариант: 0.0108 sec
Обе выполнил с ключом SQL_NO_CACHE
Gas
может по одной?
LONGMAN
Вполне нормальный explain, просто нужно понимать ход выполнения, type=ALL при джойне derived запроса будет всегда, потому что данные полученные таким запросом находятся в памяти, нет там никаких индексов. Но с учётом того, что именно к этим данным происходит привязка других таблиц, то какой type совершенно не важно. Using temporary; Using filesort - для нескольких десятков записей совершенно не страшно.
Ты бы сначала написал время выполнения запроса и что ты его хочешь ускорить, а то было очень похоже на абстрактную задачу про растолковывание значений в explain'е.
Wicked, ты скорее всего это всё знаешь, но вдруг кому тоже будет интересно
-~{}~ 05.08.10 11:26:
Вполне нормальный explain, просто нужно понимать ход выполнения, type=ALL при джойне derived запроса будет всегда, потому что данные полученные таким запросом находятся в памяти, нет там никаких индексов. Но с учётом того, что именно к этим данным происходит привязка других таблиц, то какой type совершенно не важно. Using temporary; Using filesort - для нескольких десятков записей совершенно не страшно.
Ты бы сначала написал время выполнения запроса и что ты его хочешь ускорить, а то было очень похоже на абстрактную задачу про растолковывание значений в explain'е.
может подзапросы? а то вложенные запросы и подзапросы суть разные вещи. Кстати, в mysql 5.4 они ускорили подзапросы, только не понятно, касается это correlated subquery или нет.
Wicked, ты скорее всего это всё знаешь, но вдруг кому тоже будет интересно
-~{}~ 05.08.10 11:26:
по идее не должен, данные из вложенного запроса без индексов, мне кажется в любом случае filesort будет, хотя он и не страшен на таком количестве записей.
LONGMAN
Dark Side of the Moon..
Gas
Спасибо за пояснений. О таких тонкостях MySQL в мануалах не прочтёшь
-~{}~ 05.08.10 13:48:
Wicked
Абсолютно устраивает
-~{}~ 05.08.10 13:55:
Еще есть такой запрос:
[sql]
SELECT `p`.*
FROM `shoppingcart_products` AS p
LEFT JOIN `shoppingcart_products_data` AS `d`
ON `p`.`id`=`d`.`itemid`
LEFT JOIN `shoppingcart_products_categories` AS `pc`
ON `p`.`id`=`pc`.`pid`
LEFT JOIN `shoppingcart_categories` AS `cc`
ON `cc`.`id` = `pc`.`cid`
LEFT JOIN `shoppingcart_currency` AS `cur`
ON `cur`.`id` = `p`.`currency`
WHERE ( (`cc`.`left_key`>=2
AND `cc`.`right_key`<=217) )
AND (`p`.`published`="1")
AND (`p`.`id`!="0")
AND (`p`.`smallimg` != "")
AND (`d`.`lang` = "1")
GROUP BY `p`.`id`
ORDER BY `p`.`date` DESC
LIMIT 3
[/sql]
Explain:
Запрос выполняется > 1.0 sec
Спасибо за пояснений. О таких тонкостях MySQL в мануалах не прочтёшь
-~{}~ 05.08.10 13:48:
Wicked
Абсолютно устраивает
-~{}~ 05.08.10 13:55:
Еще есть такой запрос:
[sql]
SELECT `p`.*
FROM `shoppingcart_products` AS p
LEFT JOIN `shoppingcart_products_data` AS `d`
ON `p`.`id`=`d`.`itemid`
LEFT JOIN `shoppingcart_products_categories` AS `pc`
ON `p`.`id`=`pc`.`pid`
LEFT JOIN `shoppingcart_categories` AS `cc`
ON `cc`.`id` = `pc`.`cid`
LEFT JOIN `shoppingcart_currency` AS `cur`
ON `cur`.`id` = `p`.`currency`
WHERE ( (`cc`.`left_key`>=2
AND `cc`.`right_key`<=217) )
AND (`p`.`published`="1")
AND (`p`.`id`!="0")
AND (`p`.`smallimg` != "")
AND (`d`.`lang` = "1")
GROUP BY `p`.`id`
ORDER BY `p`.`date` DESC
LIMIT 3
[/sql]
Explain:
Код:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE d ref lang,itemid lang 4 const 2122 Using where; Using temporary; Using filesort
1 SIMPLE p eq_ref PRIMARY,publish_idx,smallimg PRIMARY 4 mydb.d.itemid 1 Using where
1 SIMPLE cur eq_ref PRIMARY PRIMARY 4 mydb.p.currency 1 Using index
1 SIMPLE pc ref PRIMARY PRIMARY 4 mydb.p.id 2 Using where; Using index
1 SIMPLE cc eq_ref PRIMARY,idx_lft_rgt PRIMARY 4 mydb.pc.cid 1 Using where
Wicked
Новичок
откуда такая любовь к left join?
а этот я бы разбил на такие:
1) выбор left, right верхней категории
2) выбор вложенных категорий по left, right, но только не так, как у тебя, а по условию
`cc`.`left_key` >= 2 AND `cc`.`left_key` <= 217
причем нужно иметь индекс по left_key
3) дурацкий запрос почти без использования индексов: from pc inner join p on (... = ...) where pc.cid in (...) and `p`.`published` = "1" and `p`.`id` != "0" and `p`.`smallimg` != "" order by `date` desc limit 3;
4) либо довыбираем все остальное, либо исползуем 3й запрос в качестве подзапроса, предоставляющего 3 id-шника продуктов. Соотв-но из внешнего запроса у нас уходит все, что касается определения продуктов, категорий, сортировки, лимита.
а этот я бы разбил на такие:
1) выбор left, right верхней категории
2) выбор вложенных категорий по left, right, но только не так, как у тебя, а по условию
`cc`.`left_key` >= 2 AND `cc`.`left_key` <= 217
причем нужно иметь индекс по left_key
3) дурацкий запрос почти без использования индексов: from pc inner join p on (... = ...) where pc.cid in (...) and `p`.`published` = "1" and `p`.`id` != "0" and `p`.`smallimg` != "" order by `date` desc limit 3;
4) либо довыбираем все остальное, либо исползуем 3й запрос в качестве подзапроса, предоставляющего 3 id-шника продуктов. Соотв-но из внешнего запроса у нас уходит все, что касается определения продуктов, категорий, сортировки, лимита.
