1

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

5 Answers 5

3

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.

0
1

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).

2
  • 1
    If there are indexes on id, created_by, updated_by and deleted_by the first one can be done with a parallel run over the four indexes. The second one would need as much runs on the id index as there are users times three, which is in most case larger than four. Plus the overhead to transfer the data. So I'd rather say, especially when the table is large, the first approach is to be preferred.
    – sticky bit
    Commented May 17, 2018 at 12:05
  • @stickybit - There is no "in parallel" in MySQL. An index on created_by (etc) is of no use (for this query).
    – Rick James
    Commented May 28, 2018 at 19:04
1

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;
0
1

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.

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.