Any optimization Idea for this SQL (mySQL)?
I know the best way would be not to have any sql inside other sql, specially a complicated one, and would be better to use the analysis after the query, but because the way the system is build I would preffer to do it only on one query
Here is the query in question ("is a really cool query")
SELECT DISTINCT
o.orders_id,
o.faltantes,
o.customers_name,
o.payment_method,
o.date_purchased,
o.last_modified,
o.currency,
o.currency_value,
ot.text AS order_total,
st.stores_name
FROM (orders o
LEFT JOIN orders_total ot
ON (o.orders_id = ot.orders_id),
stores st,
orders_status s)
INNER JOIN orders_products op
ON (o.orders_id = op.orders_id)
WHERE o.orders_status != 19
AND ot.class = 'ot_total'
AND (ot.value > (SELECT
SUM(pc.monto) 开发者_运维问答 +SUM(p.monto)
FROM pagos_clientes pc,
pagos p
WHERE p.id_pago = pc.id_pago
AND pc.id_oferta = o.orders_id)
OR (SELECT
SUM(pc.monto) +SUM(p.monto)
FROM pagos_clientes pc,
pagos p
WHERE p.id_pago = pc.id_pago
AND pc.id_oferta = o.orders_id)IS NULL)
AND payment_method != 'Cambio por faltantes'
AND o.orders_stores_id = '3'
AND o.orders_stores_id = st.stores_id
AND ((o.orders_stores_id IN(1,3))
OR (op.products_distributors_id = '0'))
GROUP BY o.orders_id
ORDER BY o.orders_id DESC
Thanks to anybody that can solve this question :D (currently it takes arround 2 minutes Real Time)
Usually it's a big improvement to move a subquery into FROM. http://dev.mysql.com/doc/refman/5.1/en/rewriting-subqueries.html
Don't use comma joins, mix of proper JOINs and comma joins often breaks.
And I see that orders_status s table hasn't relation to any other table.
Adding a proper relation should increase performance many times.
Use EXPLAIN to analyze query performance and find a places for optimizations.
The output of your EXPLAIN
shows that there are not enough indexes for your query to be able to run without performing full table scans. The worst offender is the orders_products
table which does not have a suitable index on the orders_id
column.
You should check that each column used in your JOIN
conditions has an index. This alone will help a great deal.
精彩评论