my mySQL query takes too much time (about 12 seconds) - any suggestions

Question

I have a problem with a mySQL query, as it takes too much times (about 12 seconds) the table have about 70,000 records

here is my query:

SELECT  DATE(`orders`.`date`) AS `dater`,
        COUNT(*) AS `amount`,
        ( SELECT SUM(`amount`) FROM `payment`
              WHERE DATE(`dater`)=DATE(`payment`.`posted_date`)
        ) AS `charge`
    FROM `orders`
    GROUP BY `dater`
    ORDER BY `dater` DESC
    LIMIT 0,10

As you can see there is 2 tables 1. table: orders it is the orders tables here we have 1 main column: date (we need here to count the orders per day)

sample:

--------------------
date        | id
--------------------
01-01-2017  | 1
--------------------
01-01-2017  | 2
--------------------
01-02-2017  | 3
--------------------

2. table: payment: it is the payments table here we have 2 main columns: posted_date, amount (we need here to get the sum of amount field for the every day)

sample:

--------------------
posted_date | amount
--------------------
01-01-2017  | 100
--------------------
01-01-2017  | 50
--------------------
01-02-2017  | 200
--------------------

so at end the results should be [date, amount, charge]

sure in less time, as it is impossible to go with this 12 seconds :D

I see that the problem is on this internal SELECT:

(SELECT SUM(`amount`) FROM `payment`
    WHERE DATE(`dater`)=DATE(`payment`.`posted_date`)
) AS `charge`

any suggestion of how can I avoid making SELECT inside the SELECT query?

Answer 1

You have a correlated subquery. MySQL's query planner handles those in a naive way, to put it politely.

Refactor your query to use a join instead. Your correlated subquery would look like this as a joinable query.

           SELECT SUM(amount) charge,
                  DATE(posted_date) posted_date
             FROM payment
            GROUP BY DATE(posted_date)

This gets one row per day from the payment table.

Next, you need to get a similar sort of result from your orders table.

         SELECT DATE(date) AS dater,
                COUNT(*) AS amount
           FROM orders
          GROUP BY DATE(date)

Then, join those two together

SELECT a.dater, a.amount, b.charge
  FROM (
         SELECT DATE(date) AS dater,
                COUNT(*) AS amount
           FROM orders
          GROUP BY DATE(date)
       ) a
  LEFT JOIN (
           SELECT SUM(amount) charge,
                  DATE(posted_date) posted_date
             FROM payment
            GROUP BY DATE(posted_date)
       ) b ON a.dater = b.posted_date
 ORDER BY a.dater DESC
 LIMIT 0,10

It's necessary to join two subqueries here, because you need two aggregates by date for the join to work.