Mysql – Performance: Left joining same table multiple times

join;MySQLperformance

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

Best Answer

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

Related Solutions

Sql-server – Will performance be better if I specify a filter multiple times

In general there will be no difference. SQL is a declarative language that gets translated into an "execution plan" by the query optimizer. The goal of the optimizer is to provide the best execution plan the produces the results that were asked for in the query. That means that two logically equivalent queries should always produce the same execution plan.

So far the theory. In praxis however, there are way to many ways to build an execution plan for an even moderately complex query. (See for example my answer here: https://stackoverflow.com/questions/16974241/sql-server-join-selects-slower-than-join-select-with-local-table/16974641#16974641) That means that instead of aiming for the best plan, the optimizer can only aim for a good enough plan. The plans the optimizer is even considering are selected based on a set of rules and heuristics and are certainly influenced by the original query.

In SQL Server 2000 a lot of the performance optimization strategies were build around helping the optimizer to start with a better plan set by rewriting the query into another logically equivalent form. Since SQL 2005 the optimizer rarely needs that form of help anymore, but there are still a few queries out there that could benefit from a rewrite.

So to answer your question, if you observed that a query got faster by supplying that additional "hint" in the join condition, the above should give you an explanation. In general this is not the first thing to look at when trying to optimize a query.

Instead you should first make sure that your tables are properly indexed and all statistics are up to date.

MYSQL joining 3 tables with left join

I suggest you first read this question and answer Help with this query.

Then try to rewrite your queries using Option 3 (or all of the options). The following is a first attempt at option 1:

SELECT 
    p.id_pupil
  , p.name
  , p.surname
  , ROUND(AVG(q.calificacion),2) AS average
  , COUNT(DISTINCT q.id_qualification) AS count
  , COUNT(DISTINCT CASE WHEN i.type_incident = 'miss'        THEN i.id_incidents END)
       AS misses
  , COUNT(DISTINCT CASE WHEN i.type_incident = 'delay'       THEN i.id_incidents END)
       AS delays
  , COUNT(DISTINCT CASE WHEN i.type_incident = 'attitude'    THEN i.id_incidents END)
       AS attitude
  , COUNT(DISTINCT CASE WHEN i.type_incident = 'miss_justif' THEN i.id_incidents END)
       AS misses_justificadas
FROM 
    pupils                   AS p 
    LEFT JOIN incidents      AS i  ON  i.id_pupil = p.id_pupil
                                   AND i.id_trimester = 1
    LEFT JOIN qualifications AS q  ON  q.id_pupil = p.id_pupil
                                   AND q.id_trimester = 1
                                   AND q.type_qualification = 'class' 
WHERE 
    p.level = 1
    AND p.class = 'A' 
    AND p.id_trimester = 1 
GROUP BY 
    p.id_pupil 
  , p.name
  , p.surname ;

Related Question