CODEWITHSUNDEEP
mysql
carmelocony
carmelocony

Reputation: 57

In mysql use MAX function from other table

I wrote an example to describe my problem in a more complex database. I use MySQL 5.7 with Delphi XE8.

I have 2 tables:

CREATE TABLE customers 
(ID   INT         NOT NULL   AUTO_INCREMENT , 
Name VARCHAR(30)  NOT NULL , 
PRIMARY KEY (ID)
) ENGINE = InnoDB;

CREATE TABLE orders
(IDorders    INT        NOT NULL   AUTO_INCREMENT ,
customerID   INT        NOT NULL , 
Description VARCHAR(30) NOT NULL ,
DateOrder    Date       NOT NULL , 
PRIMARY KEY (IDorders),
INDEX DateOrderIndex (DateOrder, customerID) USING BTREE;
) ENGINE = InnoDB;


select *,
       (SELECT MAX(DateOrder) FROM Orders  WHERE Orders.customerID = 
customers.ID) as LastOrder
FROM customers

My problem is: the customer table has 58,000 records and the orders table has 200,000 records The query result occurs after 28 seconds. Where am I doing wrong?

Upvotes: 0

Views: 42

Answers (1)

D-Shih
D-Shih

Reputation: 46219

You can try to use JOIN with MAX and GROUP BY in subquery.

select c.*,
       t1.maxDt
FROM customers c 
JOIN (
    SELECT customerID,MAX(DateOrder) maxDt
    FROM Orders 
    GROUP BY customerID
) t1 on t1.customerID = c.ID

Note

if your query is slow you can try to create indexs on Orders.customerID and customers.ID

Upvotes: 2

Related Questions