CODEWITHSUNDEEP
mysqlindexing
Dylan
Dylan

Reputation: 9383

MySQL : do I need indexes in this situation?

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

Answers (2)

gbn
gbn

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

KingCronus
KingCronus

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

Related Questions