Медленный запрос SQL, как его оптимизировать?

Question

[stoletvobed911]

Доброго времени суток!
Знакомый попросил сделать хоть что-нибудь, чтобы перестала тормозить корзина в OpenCart.
Я так себе программист, на самом деле, но при помощи профилирования понял, что почти 10 секунд выполняется следующий запрос:

DELETE
	order,
	order_product,
	order_option,
	order_download,
	order_total,
	order_voucher
FROM order
LEFT JOIN order_product ON order_product.order_id = `order`.order_id
LEFT JOIN order_option ON order_option.order_id = `order`.order_id
LEFT JOIN order_download ON order_download.order_id = `order`.order_id
LEFT JOIN order_total ON order_total.order_id = `order`.order_id
LEFT JOIN order_voucher ON order_voucher.order_id = `order`.order_id
WHERE
	order.order_id = '718707' AND order.order_status_id = 0

Помогите его оптимизировать, пожалуйста. Что нужно сделать?

Answer 1

[Rsa97]

Для всех подчинённых таблиц представить связь через FOREIGN KEY ON DELETE CASCADE и удалять только запись из `order`

Comments

[stoletvobed911]

Rsa97 это хороший вариант, но я не могу править БД

[Rsa97]

stoletvobed911: Тогда остаётся разбить запрос на простые удаления, лучше внутри транзакции. Открываете транзакцию, удаляете запись из `order`, смотрите affected_rows, если не 0, то удаляете записи из остальных таблиц. Закрываете транзакцию.

Answer 2

[feldwebel]

explain analyze <sql>
и смотри, где идёт seq scan и в целом тратится больше всего времени.

Если лень разбираться с темой, попробуй метод научного тыка. На реплике базы поиграйся с индексами на order.order_id и order.order_status_id, а потом на order_id других джойнящихся таблиц, начиная с самых жирных.

Может и наоборот, таблицы излишне заиндексированы, удаление строки приводит к тяжёлой перестройке индексов.
Так что делай explain и гугли.

Answer 3

[Александр N++]

Сделайте селект с explain у вас LEFT JOIN таблицы не фулскан случаем проходят?

Ну если нужно хард удаление то я бы средствами бд сделал
cascade delete mysql
При удалении из order записи все что связано удалится.

Comments

[stoletvobed911]

Александр N++ ваше первое предложение я вообще не понял :(

[Игорь]

постройте план выполнения запроса (EXPLAIN), там будет видно на что расходуются ресурсы. Потом можно будет думать, как оптимизировать запрос.

[stoletvobed911]

Игорь: а могу я просто сделать несколько простых запросов?
Сначала вытащить order_id из order который order_status_id = 0, а потом уже сделать по 1 delete для каждой таблицы.
Так же быстрее должно стать, верно? Не будет больше никаких join'ов.

[Игорь]

stoletvobed911: да, конечно, этот запрос можно разбить на несколько простых.

Answer 4

[nozzy]

Здесь напрашивается INNER JOIN вместо LEFT JOIN.

Answer 5

[Serdonda]

А про индексы не забыли?
Самое первое - проставьте индексы по всем связанным полям - 100% время выполнения упадет до значиний меньше десятых секунды.

Comments

[AlikDex]

зависит от количества записей. Если в каждой таблице по пять-десять миллионов записей, до запрос будет довольно медленный. А если еще и с конца таблицы удалять. То счет может вестись на секунды даже с индексами. К примеру тестировал запрос с тремя джойнами на 25 миллионах записей в одной таблице, и 400тысячами записями в другой (в третьей и четвертой всего по 60). Так вот на 2-х процессорном i-7 с 8 ядрами на каждом запрос в конец таблицы выполнялся 0.6секунды. Что довольно много для запроса. Причем дополнительных параметров с сортировками небыло.

Answer 6

[Антон Тихомиров]

А что мешает из каждой таблицы удалять нужные строки отдельными запросами? Ведь это буквально доли секунды на каждый запрос, даже если индексов вообще нет. Ну и да, индексы проставить нужно в любом случае.

P. S. Ну а вообще, сколько я вижу дебри OpenCart, все больше склоняюсь к мысли, что ее разработка началась еще когда разраб учился в школе... Грустно это все...