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.