#mysql
#mysql
Вопрос:
ВЫБЕРИТЕ (inv_total — isc_total) КАК прибыль ОТ (
ВЫБЕРИТЕ
SUM(inv.net_weight * inv.unit_price) КАК isc_total,
SUM(СЛУЧАЙ, КОГДА (i.».$ex_gst.»ex_gst = ‘YES’)
ЗАТЕМ (inv.net_weight * (СЛУЧАЙ, КОГДА (i.final = ‘Y’ И i.currency_id НЕ РАВНО NULL) ЗАТЕМ (ind.final_unit_price * cr.currency_value)
КОГДА (i.final = ‘Y’ И i.currency_id РАВНО NULL) ЗАТЕМ ind.final_unit_price
КОГДА (i.final<> ‘Y’ И i.currency_id НЕ РАВНО НУЛЮ) ЗАТЕМ (ind.unit_price * cr.currency_value)
КОГДА (i.final <> ‘Y’ И i.currency_id РАВНО НУЛЮ) ЗАТЕМ ind.unit_price ЕЩЕ ind.unit_price END ) * 1.06)
ЕЩЕ (inv.net_weight * (СЛУЧАЙ, КОГДА (i.final = ‘Y’ И i.currency_id НЕ РАВНО НУЛЮ), ЗАТЕМ (ind.final_unit_price * cr.currency_value)
, КОГДА (i.final = ‘Y’ И i.currency_id РАВНО НУЛЮ), ЗАТЕМ ind.final_unit_price,
КОГДА (i.final <> ‘Y’ И i.currency_id НЕ РАВЕН НУЛЮ), ЗАТЕМ (ind.unit_price * cr.currency_value)
, КОГДА (i.final <> ‘Y’ И i.currency_id РАВНО НУЛЮ), ЗАТЕМ ind.unit_price
ELSE ind.unit_price END )
) END
)КАК inv_total
ИЗ «.$prefix_qry.»inventory inv
ВНУТРЕННЕЕ СОЕДИНЕНИЕ «.$prefix_qry.»osc_detail osc_do НА osc_do.inventory_id = inv.inventory_id
ВНУТРЕННЕЕ СОЕДИНЕНИЕ продукта p НА inv.product_id = p.product_id
ЛЕВОЕ СОЕДИНЕНИЕ «.$prefix_qry.»iscisc НА inv.isc_batch_no = isc.isc_batch_no
ВНУТРЕННЕЕ СОЕДИНЕНИЕ поставщиков НА isc.supplier_id = s.supplier_id
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ПОСТАВЩИКОВ «.$prefix_qry.»счет i НА inv.osc_id = i.osc_id
ВНУТРЕННЕЕ СОЕДИНЕНИЕ «.$prefix_qry.»invoice_detail ind НА i.invoice_id = ind.invoice_id И ind.product_id = p.product_id
СЛЕВА ПРИСОЕДИНИТЕ currency cr К i.currency_id = cr.currency_id
, ГДЕ inv.osc_id !=»
И inv.inventory_type = ‘ПОДТВЕРДИТЬ’
И i.cancel_by РАВНО НУЛЮ
, А s.supplier_id = ‘».$supp_id.»‘
И МЕСЯЦ (i.date_of_delivery) = ‘».$month.»‘
И YEAR(i.date_of_delivery) = ‘».$ year .»‘) В КАЧЕСТВЕ
Комментарии:
1. Спасибо, что поделились отчетом о состоянии. Ваш запрос выполняется медленно. Был ли вопрос ?
Ответ №1:
Чтобы позволить MySQL эффективно использовать сканирование диапазона в индексе с date_of_delivery в качестве ведущего столбца, предполагая, что $ year представляет действительный год, а $ month — значение от 1 до 12, затем измените это:
AND MONTH(i.date_of_delivery) = '".$month."'
AND YEAR(i.date_of_delivery) = '".$year ."'
К чему-то вроде этого:
AND i.date_of_delivery >= STR_TO_DATE('".$year."-".$month."-01','%Y-%m-%d')
AND i.date_of_delivery < STR_TO_DATE('".$year."-".$month."-01','%Y-%m-%d')
INTERVAL 1 MONTH
«Внешность» LEFT JOIN to isc отрицается последующим внутренним соединением to s . То есть эквивалентный результат будет получен при замене LEFT JOIN на INNER JOIN .
Убедитесь, что подходящие индексы доступны и используются.
В выводе EXPLAIN будет показан план выполнения.
Кроме этого, нам действительно нужно было бы увидеть результат EXPLAIN. Это даст нам представление о том, какие индексы доступны, статистика таблиц и так далее. Чтобы действительно углубиться, нам нужно было бы знать определения всех индексов для всех таблиц, участвующих в запросе, и типы данных столбцов, участвующих в предикатах.
Учитывая, что запрос встроенного представления возвращает одну строку, накладные расходы производной таблицы незначительны.
Этот запрос должен вернуть результат, эквивалентный исходному встроенному представлению.
SELECT SUM( inv.net_weight
* inv.unit_price
) AS isc_total
, SUM( inv.net_weight
* IF(( i.".$ex_gst."ex_gst = 'YES' ), 1.06, 1.00)
* IF(( i.final = 'Y' ), ind.final_unit_price, ind.unit_price)
* IF(( i.final IS NULL OR i.currency_id IS NULL ), 1.00, cr.currency_value)
) AS inv_total
FROM ".$prefix_qry."invoice i
JOIN ".$prefix_qry."invoice_detail ind
ON ind.invoice_id = i.invoice_id
JOIN ".$prefix_qry."inventory inv
ON inv.product_id = ind.product_id
AND inv.inventory_type = 'CONFIRM'
AND inv.osc_id = i.osc_id
AND inv.osc_id != ''
JOIN ".$prefix_qry."osc_detail osc_do
ON osc_do.inventory_id = inv.inventory_id
JOIN ".$prefix_qry."isc isc
ON isc.supplier_id = '".$supp_id."'
AND isc.isc_batch_no = inv.isc_batch_no
JOIN supplier s
ON s.supplier_id = '".$supp_id."'
AND s.supplier_id = isc.supplier_id
JOIN product p
ON p.product_id = inv.product_id
AND p.product_id = ind.product_id
LEFT
JOIN currency cr
ON cr.currency_id = i.currency_id
WHERE i.cancel_by IS NULL
AND i.osc_id != ''
AND i.date_of_delivery >= DATE_FROM_STR('".$year."-".$month."-01','%Y-%m-%d')
AND i.date_of_delivery < DATE_FROM_STR('".$year."-".$month."-01','%Y-%m-%d')
INTERVAL 1 MONTH
Сводка использования столбцов в списке ВЫБОРА
-------- ---------------------------------------------------------------
i final, currency_id, $[ex_gst]ex_gst
inv net_weight,unit_price
cr currency_value
Сводка использования столбцов в ПРЕДИКАТАХ
-------- ---------------------------------------------------------------
i osc_id, invoice_id, cancel_by, date_of_delivery
ind invoice_id
inv inventory_id, isc_batch_no, osc_id, inventory_type, product_id
isc isc_batch_no, supplier_id
osc_do inventory_id
p product_id
s supplier_id
cr currency_id
Вероятно, эти таблицы меньше и уже имеют эти столбцы в качестве ПЕРВИЧНОГО КЛЮЧА
... ON p (product_id)
... ON s (supplier_id)
... ON cr (currency_id)
Мы должны рассмотреть, необходимо ли включать таблицы product ( p ) и supplier ( s ) в запрос. Если столбцы, используемые (показанные выше) для p и s , являются ПЕРВИЧНЫМ КЛЮЧОМ или УНИКАЛЬНЫМ КЛЮЧОМ, и если применяются эти ограничения внешнего ключа:
inv (product_id) references p (product_id)
ind (product_id) references p (product_id)
isc (supplier_id) references s (supplier_id)
тогда соединения с p и s не повлияют на результат. То есть эти таблицы могут быть удалены из запроса.
Первое сокращение при покрытии индексов, которое может помочь оптимизатору сгенерировать эффективный план выполнения…
... ON i (date_of_delivery,cancel_by,osc_id,invoice_id,final,currency_id,[$ex_gst]ex_gst)
... ON ind (invoice_id, product_id)
... ON inv (inventory_id,inventory_type,isc_batch_no,osc_id,inventory_type,product_id,net_weight,unit_price)
... ON isc (supplier_id,isc_batch_no)
... ON osc_do (inventory_id)