Uptimize select product details each customer purchased from sales tables

73
January 26, 2019, at 00:00 AM

I have 4 tables, Customers, Products, Sales & Sale_Items. I pull data from it using the query below.

SELECT (
            SELECT c.name 
            FROM Customers c
            WHERE s.customer_id=c.id
            ) customer
    ,(
        Select group_concat(description)
        FROM (
            SELECT si.id
                ,si.sale_id
                ,concat("x", si.Qty, " ", p.name, " ",(si.total)) description
            FROM Sale_Items si
            LEFT JOIN Products p ON p.id = si.product_id
            ) p
        where s.id = Sale_ID
        GROUP BY Sale_ID
        ) detail,
                            s.total
FROM Sales s

The query produces the result, but it becomes slow with just 2000 records (takes 114 seconds to finish)

Customer    Product            Total
--------------------------------------
James       x1 ItemA 10.00  75.00
            x3 ItemB 15.00
            x1 ItemC 20.00
Mark        x2 ItemA 10.00  50.00
            x2 ItemB 15.00
Bisi        x1 ItemC 20.00  30.00
            x2 ItemA 10.00

how can i make this faster?

An attempt has been made here

https://www.db-fiddle.com/f/pkL2HtsT659EXgRSevFSAm/4

Answer 1

If we want to stick with correlated subqueries, we can eliminate the inline view p.

That's going to get materialized for every row retrieved from Sales. The predicate in the WHERE clause in the outer query doesn't get "pushed" down into the view. So the materialized view (or "derived table" in the MySQL parlance) is going to be a full set, and from that, we're going to pick out just a few rows. And we're going to repeat that for each row from Sales.

Unwinding that derived table should give us some performance benefit. This would be reasonable approach for a small number of rows returned from Sales, with suitable indexes defined. That is, if we were limiting the number of rows examined by the outer query with a WHERE clause. With a large number of rows, those correlated subqueries are going to drag down performance.

SELECT ( SELECT c.name 
           FROM Customers c
          WHERE c.id = s.customer_id
       ) AS customer
     , ( SELECT GROUP_CONCAT(CONCAT('x',si.Qty,' ',p.name,' ',si.total) ORDER BY p.name SEPARATOR '\r\n')
           FROM Sale_Items si
           LEFT
           JOIN Products p
             ON p.id = si.product_id
          WHERE si.sale_id = s.id
       ) AS detail
     , s.total
  FROM Sales s
 WHERE ... 
 ORDER
    BY ...

If the query is returning all rows from Sales and we are doing the whole bloomin' set, then I'd tend to avoid the correlated subqueries. (That's because the subqueries gets executed for each and every row returned by the outer query. Those subqueries are going to eat our lunch, in terms of performance, with a large number of rows returned.)

Assuming id is unique in customers, we're usually much better off with a join operation.

SELECT c.name AS customer
     , d.detail
     , s.total
  FROM Sales s
  LEFT
  JOIN Customers c
    ON c.id = s.customer_id
  LEFT
  JOIN ( SELECT si.sale_id
              , GROUP_CONCAT(CONCAT('x',si.Qty,' ',p.name,' ',si.total) ORDER BY p.name SEPARATOR '\r\n') AS detail
           FROM Sale_Items si
           LEFT
           JOIN Products p
             ON p.id = si.product_id
          GROUP
             BY si.sale_id
       ) d
    ON d.sale_id = s.id
 ORDER
    BY ...

The inline view d is going to be expensive with large sets; but at least we're only doing that query one time, materializing the results into a "derived table". Then the outer query can run, and retrieve rows from the derived table.

READ ALSO
How to automatically connect to Cloud SQL via the proxy?

How to automatically connect to Cloud SQL via the proxy?

I have mySQL database set in settingspy like this:

85
possible to calculate average

possible to calculate average

i have following sample database:

75
MySQL VIEW, using prior calculation with avoiding a nested VIEW

MySQL VIEW, using prior calculation with avoiding a nested VIEW

Is it possible to use a prior calculation in a MySQL VIEW? I've done a bunch of playing with this and searching online (including on SO), but I'm coming up blank

68
Backup MySQL database with OHO Codeigniter

Backup MySQL database with OHO Codeigniter

i have a problem to determine the destination where the zip backup file should be downloaded, and the name of the zip file are always generated randomly like this one i got 0ca26f32-b90c-4198-b078-ed2778a23c0bzip but the sql file inside the zipped folder...

62