Left joining same table multiple times

Question

Let's say I've the following table:

id | name  | created_by  | updated_by | deleted_by
--------------------------------------------------
1  | alice | NULL        | NULL       | NULL       
2  | ben   | NULL        | 3          | 1          
3  | kane  | 2           | NULL       | 3          
4  | ali   | 1           | 1          | NULL       
5  | peter | 1           | 1          | 3          
6  | rose  | 1           | 1          | 5          
.
.

Approach 1: I'd run a query like this:

SELECT 
u.*, a.name as 'creator', b.name as 'updator', c.name as 'deletor'
FROM users u
LEFT JOIN users a on u.created_by = a.id
LEFT JOIN users b on u.updated_by = b.id
LEFT JOIN users c on u.deleted_by = c.id
ORDER BY u.id
LIMIT 10

If the table has 1000-10000 records, primary id, is the query bad or good?

Approach 2: Or is it better to fetch without left joining, then on application level, I do another query to fetch the creator/updator/deletor, like so:

Query 1:

 SELECT 
u.*
FROM users u
ORDER BY u.id
LIMIT 10

Query 2:

SELECT 
u.name
FROM users u
WHERE `id` IN ($ids) # $ids is an array of all created_by+updated_by+deleted_by from query 1
ORDER BY u.id
LIMIT 10

Answer 1

To ensure max performance (assuming id is primary/unique) I'll recommend to move LIMITation into subquery:

SELECT 
u.*, a.name as 'creator', b.name as 'updator', c.name as 'deletor'
FROM (SELECT * FROM users ORDER BY id LIMIT 10) u
LEFT JOIN users a on u.created_by = a.id
LEFT JOIN users b on u.updated_by = b.id
LEFT JOIN users c on u.deleted_by = c.id
ORDER BY u.id

Filesort of 10 records can't influence on performance.

Answer 2

Approach 1 will be better in your scenario, as it will give the result by one query run and seems to be properly joined as required by the business, though same table is joined multiple times.An index on id column will perform better.

Approach 2 with order by will require some more space in temp db for processing and the IN operator will degrade the performance in query 2 if the #ids has a lot of values returned within it(say there are large number of updated,deleted or created users returned by query 1).

Answer 3

If and only if you stick to "LIMIT 10", I'd go for

SELECT u.*,
  (select a.name from user a where u.created_by = a.id) as 'creator',
  (select a.name from user a where u.updated_by = a.id) as 'updater',
  (select a.name from user a where u.deleted_by = a.id) as 'deleter'
  FROM users u
  ORDER BY u.id
  LIMIT 10;

Answer 4

For ORDER BY u.id LIMIT 10, all the choices are fast enough to not matter.

I argue against Approach 2 because it is more complex -- both in SQL and the app. Why have extra 'moving parts' that could have bugs in them?

Without LIMIT 10, try Approach 1 and Gerald's. Then report back to us. I predict that Approach 1 or Gerald's will win; I don't know which -- but I would like to know which.

Answer 5

was faced with a similar predicament using SQL 2008R2. The Main transaction table has three columns that are related to another table Notes. My solution was to create a subquery for the columns of concern and outer join to the main table. Tbl1 has three columns GId, VId and BId which relate to the primary key FieldID in Tbl2. The value we want for the report is kept in Field2

Select   L.Field1, L.Field2, L.Field3, --- Other fields
(SELECT Notes FROM Tbl2 N WHERE N.FieldId = L.GID) AS GeneralNote, 
(SELECT Notes FROM Tbl2 V WHERE V.FieldId = L.VId) AS VendorNote,  
(SELECT Notes FROM Tbl2 B WHERE B.FieldId = L.BId) AS BuyerNote

FROM  Tbl1 L    
Left Outer Join Tbl2 N on L.G_Id = N.NoteId
Left Outer Join Tbl2 V on L.V_Id = V.NoteId
Left Outer Join Tbl2 B on L.B_Id = B.NoteId

Hope this helps