Querying a "latest order" in an optimized way - Mysql

AntonS
March 6, 2023
220 views
0 votes
2 Answers

(MySQL, InnoDB) Database has the tables Client (id, name) and Order (id, client_id, name, order_date).

I would like to query them to receive a list of Client and one, latest Order for each Client, or none if there is none for that Client (left join).

There are additional constraints, joins and columns on the selected rows, but they should not be getting in the way of the Client-Order join, which is direct.

The problem is, even with the constraints, it’s picking 1000+ rows from a 100 000+ row total selection, and it takes 15+ minutes to return. When I try to export it to a CSV file, it fails to do it after even an hour.

Here’s what I tried:

SELECT DISTINCT 
    Client.id as client_id, 
    Client.name as client_name,
    Order.name as order 
FROM 
    Client
LEFT JOIN 
    Order ON Order.id = (SELECT O2.id FROM Order O2
                         <... joins and clauses for side tables ...>
                         WHERE O2.orderDate >= '2021-01-01' 
                           AND O2.orderDate <= '2022-01-01'
                           AND O2.client_id = Client.id
                         ORDER BY O2.id DESC
                         LIMIT 1)
<... joins and clauses for side tables ...>;

I’ve googled and the say subqueries are inefficient, so is there a way to query this without one and/or in a more efficient way?

Edit: I am using MySQL8 with DBeaver 22.2.2. Simply querying the data and outputting into the DBeaver window takes around 11 minutes. If I then right-click it and select "Export", the problems begin.

Tags: mysql sql

Answers

I would extract the latest order per client using a CTE, and then query that

WITH clients_last_order (client_id, order_date) AS (
  SELECT client_id, MAX(order_date)
  FROM Orders
  GROUP BY client_id
)
SELECT Clients.id, Clients.name, Orders.id, Orders.order_date
FROM Clients
LEFT JOIN Orders ON (Clients.id = Orders.client_id)
LEFT JOIN clients_last_order ON (
  Orders.client_id = clients_last_order.client_id
  AND Orders.order_date = clients_last_order.order_date
)

I would use a left join to extract the max date then get the id of that record to join with orders again. there you go, Tested and working.

Metadata

    -- create a table
    CREATE TABLE Client (
      id INTEGER PRIMARY KEY,
      name NVARCHAR(100) NOT NULL
    );
    
    CREATE TABLE Orders(
    id int PRIMARY KEY,
    client_id INTEGER,
    name NVARCHAR(100),
    order_date DATETIME,
    foreign key (client_id) references Client(id)
    );
    INSERT INTO Client VALUES (1, 'Ryan');
    INSERT INTO Client VALUES (2, 'Joanna');
    INSERT INTO Client VALUES (3, 'Sbira');
    INSERT INTO Client VALUES (4, 'Jamal');
    
    
    INSERT INTO Orders VALUES (1, 1,'Burger', '2023-03-06');
    INSERT INTO Orders VALUES (2, 1,'Burger', '2023-03-07');
    INSERT INTO Orders VALUES (3, 1,'Burger', '2023-03-09');
    
    
    
    INSERT INTO Orders VALUES (4, 2,'Bag', '2023-03-06');
    INSERT INTO Orders VALUES (5, 2,'Tomatoes', '2023-03-07');
    INSERT INTO Orders VALUES (6, 2,'Spaghetti', '2023-03-10');
    
    INSERT INTO Orders VALUES (7, 3,'Screen', '2023-03-06');
    INSERT INTO Orders VALUES (8, 3,'Salad', '2023-03-07');
    INSERT INTO Orders VALUES (9, 3,'Cheese', '2023-03-11');

    /*Extraction script*/
    
    SELECT C.name,
           Orders.name,
           Orders.order_date
      FROM Client C
      LEFT JOIN (   SELECT o.client_id AS cid,
                           MAX(order_date) AS last_order
                      FROM Orders o
                     group by o.client_id) Orders_bis
        ON Orders_bis.cid   = C.id
      LEFT JOIN Orders
        ON order_date       = Orders_bis.last_order
       AND Orders.client_id = Orders_bis.cid

Output

client order order_date

Ryan | Burger | 2023-03-09 00:00:00

Joanna | Spaghetti | 2023-03-10 00:00:00

Sbira | Cheese 2023-03-11 00:00:00

Jamal NULL NULL

Please signup or login to give your own answer.

Click here to cancel reply.

Querying a "latest order" in an optimized way – Mysql

Answers