Performance: Left joining same table multiple times 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
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).