Reputation: 9383
I have a table 'Clients' and a sub-table 'Orders'.
For a certain view I need to display the last order for each client.
Since you cannot use LIMIT in a join, I first used a complex solution with a LEFT JOIN, GROUP_CONCAT and SUBSTRING_INDEX to get the last order, but this is quite slow, since there are millions of records.
Then I thought of just storing the last OrderID in the Clients table, that is updated by a trigger each time the Orders table changes. Then I just do a LEFT JOIN to Orders on this field LastOrderID.
Would an index on the field LastOrderID be of any use in this situation? Or wouldn't it be used since the source table is always Clients, so there is no sorting, searching, etc. done on this field ?
The reason I'm asking is that in reality it's a little bit more complex, I might actually need about 20 of these kind of fields.
update:
My query now is :
SELECT *
FROM Clients AS c
LEFT JOIN Orders AS o ON o.OrderID=c.LastOrderID
Would an index on LastOrderID in Clients improve speed, or is it not neccessary?
Upvotes: 2
Views: 89
Reputation: 432742
Perhaps your SQL is wrong?
This is standard SQL: you'll need a single two column index on (ClientID, OrderID) on the Orders table for this which will speed up the aggregate and self join
SELECT
...
FROM
(
SELECT MAX(OrderID) AS LastOrderID, ClientID
FROM Orders
GROUP BY ClientID
) o2
JOIN
Orders o ON o2.LastOrderID = o.ClientID AND o2.OrderID = o.ClientID
JOIN
Clients c PN o.ClientID = c.ClientID
Upvotes: 0
Reputation: 4529
First of all, do you have an index on the Client foreign key within the Order table?
Doing this alone should increase performance quite considerably.
Upvotes: 1