Как увеличить скорость запроса MySQL с дополнительным условием?

ProgramBox

Как увеличить скорость запроса MySQL с дополнительным условием?

Post author:admin
Запись опубликована:10 февраля, 2023
Post category:Вопросы по программированию

#php #mysql #sql #performance #query-performance

#php #mysql #sql #Производительность #запрос-производительность

Вопрос:

Я пытаюсь ускорить следующий запрос, поскольку его выполнение занимает довольно много времени: сейчас это «всего» около 1,5 секунд, но он, безусловно, будет медленнее с увеличением количества строк (что в 10 раз увеличится в течение следующего периода).

В принципе, я хочу показать все строки из таблицы orders для пользователя, и в каждой строке отображается общая сумма заказа (которая является СУММОЙ таблицы orders_products).

 SELECT 
orders.order_number,
orders.order_date, 
companies.company_name,
COALESCE(SUM(orders_products.product_price * orders_products.product_quantity),0) AS order_value 
FROM orders 
LEFT JOIN companies ON companies.id = orders.company_id
LEFT JOIN orders_products ON orders_products.order_id = orders.id
LEFT JOIN users ON users.id = orders.user_id 
WHERE orders.user_id = '$user_id'
AND companies.user_id = '$user_id'
GROUP BY orders.id ORDER BY orders.order_date DESC, orders.order_number DESC

Я попытался добавить другое условие AND orders_products.user_id = '$user_id' . Скорость запроса была примерно в 12 раз быстрее (да!), Но проблема в том, что не во всех заказах есть продукты. В этом случае заказы без продуктов в них не возвращаются.

Как мне изменить свой запрос так, чтобы, несмотря на то, что в заказе нет продуктов, он все равно возвращался (с общим значением заказа 0), одновременно ускоряя запрос?

Заранее спасибо за вашу помощь!

1. Просто для проверки: вы установили правильные индексы? Я предполагаю, что у вас есть.

2. Я не установил никаких индексов. Я слышал о них, но понятия не имею, как их установить… Поможет ли это ускорить подобные запросы?

3. Да, индексы — это СПОСОБ ускорить процесс.

4. Не связано, но есть некоторые вещи, которые я нахожу странными. Для чего вы присоединяетесь к таблице users ? Почему вы пытаетесь внешне присоединиться к таблице companies? Почему у компании есть идентификатор пользователя ?

5. @Thorsten, я присоединяюсь к таблице users, потому что мне также нужно выбрать некоторые детали для пользователя. Теперь я вижу, что я оставил этот оператор select из своего кода. Приношу свои извинения за путаницу. Я присоединяюсь к таблице companies, потому что хочу получить все заказы и присоединиться к компаниям, чтобы показать название компании в строке заказа. Должен ли я использовать другое соединение? Имена users / user_id могут немного сбивать с толку, но вкратце: у меня есть пользователи, и эти пользователи могут добавлять компании. И, в свою очередь, эти компании могут добавлять заказы. Итак, я хочу знать, какой пользователь добавил какую компанию.

Ответ №1:

Возможно, вам будет быстрее использовать коррелированный подзапрос:

 SELECT o.order_number, o.order_date,  c.company_name,
      (SELECT COALESCE(SUM(op.product_price * op.product_quantity), 0)
       FROM orders_products op
       WHERE op.order_id = o.id
      ) AS order_value 
FROM orders o LEFT JOIN
     companies c
     ON c.id = o.company_id AND c.user_id = o.user_id
WHERE o.user_id = '$user_id'
ORDER BY o.order_date DESC, o.order_number DESC

Это избавляет от внешней агрегации, которая часто является выигрышем в производительности.

Тогда для производительности вам нужны следующие индексы:

orders(user_id, order_date desc, order_number_desc, company_id)
companies(id, company_id, company_name)
orders_products(order_id, product_price, product_quantity)

1. Привет, Гордон Линофф, спасибо за ваш ответ. Я протестировал коррелированный подзапрос, но, к сожалению, он не ускоряет запрос. Старый запрос: 1,1285 секунды Новый запрос: 1,0912 секунды По сравнению с другим запросом с дополнительным предложением AND: 0,0368 секунды Любым способом добавьте дополнительное предложение AND, но все равно верните все строки orders, даже если в заказе нет продуктов?

2. @Nicholas . . . Вы бы добавили условие в ON предложение или подзапрос. Я добавил индексы, которые должны улучшить производительность. Конечно, если у одного пользователя десятки тысяч заказов, запрос займет время.

3. Я добавил условие в подзапрос следующим образом: (SELECT COALESCE(SUM(ROUND(orders_products.product_price, 2) * orders_products.product_quantity), 0) FROM orders_products WHERE orders_products.order_id = orders.id AND orders_products.user_id = '$user_id') AS order_value . Запрос выполняется, но не быстрее (1.4106 секунды). Может быть, это просто нельзя ускорить таким образом, только с помощью индексов?

4. @Nicholas . , , Вы пробовали рекомендуемые индексы?

5. Я опубликовал свой ответ непосредственно перед тем, как увидел ваш обновленный ответ. Теперь я добавил индексы, и они работают как шарм: 0,0273 секунды! Большое вам спасибо за вашу помощь!