Perf issue with Inheritance (LEFT JOIN vs INNER JOIN)

Showing 1-15 of 15 messages
Perf issue with Inheritance (LEFT JOIN vs INNER JOIN) Eric Cambray 12/21/11 9:22 AM
Hi,
I encounter a strange behaviour in performance with Doctrine 2.1.4 and inheritance.

I have this Entities :
Cv
Personn that inherit from User

I do a request on a DB with 250K+ records in it :

I do this :

$em->createQuery('SELECT cv, o
FROM Cv cv
JOIN cv.owner o
WHERE cv.completion >= 0.5
AND cv.referencing = 1
AND cv.main = 1
AND cv.culture = ?1 
ORDER BY o.id DESC');

The request take near 15 seconds.

The SQL that result is this (I have replace alias and column selection for easy reading) :

SELECT * 
FROM user_cvs cv 
INNER JOIN personns p ON cv.user_id = p.id 
LEFT JOIN users u ON p.id = u.id 
WHERE cv.completion >= 0.5 
AND cv.referencing = 1 
AND cv.main = 1 
AND cv.culture = 'fr_FR' 
ORDER BY u.id DESC LIMIT 21 OFFSET 1700;
 

If I try it directly in MySQL : 15 seconds.

Then I change the query for this :

SELECT * 
FROM user_cvs cv 
INNER JOIN personns p ON cv.user_id = p.id 
INNER JOIN users u ON p.id = u.id 
WHERE cv.completion >= 0.5 
AND cv.referencing = 1 
AND cv.main = 1 
AND cv.culture = 'fr_FR' 
ORDER BY u.id DESC LIMIT 21 OFFSET 1700;

(Just change LEFT to INNER JOIN)
Then : 0.16s (and it's not a MySQL Cache)

Do you know how to avoid this? (or why this happens?) 

Re: [doctrine-user] Perf issue with Inheritance (LEFT JOIN vs INNER JOIN) Marco Pivetta 12/21/11 3:24 PM
I'd suggest you to use EXPLAIN on that query... Maybe it's doing a sequential scan on the product of the join (and LEFT JOIN produces far more rows than INNER JOIN :P )

http://dev.mysql.com/doc/refman/5.1/en/explain.html

Using EXPLAIN is vital to get to know how MySQL will scale as your app grows, so be sure of having understood how to use it ;)

Marco Pivetta

http://twitter.com/Ocramius     

http://marco-pivetta.com    



--
You received this message because you are subscribed to the Google Groups "doctrine-user" group.
To view this discussion on the web visit https://groups.google.com/d/msg/doctrine-user/-/Sx24fUBjEJcJ.
To post to this group, send email to doctri...@googlegroups.com.
To unsubscribe from this group, send email to doctrine-use...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/doctrine-user?hl=en.

Re: [doctrine-user] Perf issue with Inheritance (LEFT JOIN vs INNER JOIN) Eric Cambray 12/22/11 12:29 AM
Hi,
thanks for your answer Marco. But my issue isn't really the query itself, but why Doctrine had generated this query from my DQL.

It seems that the inheritance made a LEFT JOIN and not a INNER JOIN. So maybe it's normal, but in my case the performance are really poor; is there a way to specify INNER JOIN instead of LEFT JOIN?

Thanks
Re: [doctrine-user] Perf issue with Inheritance (LEFT JOIN vs INNER JOIN) Marco Pivetta 12/22/11 12:41 AM
LEFT JOIN is required here as you could have more entities in the inheritance.
What you can do here is checking which one of these causes your issue:


WHERE cv.completion >= 0.5 
AND cv.referencing = 1 
AND cv.main = 1 
AND cv.culture = 'fr_FR'

As said, EXPLAIN ftw :)


Marco Pivetta

http://twitter.com/Ocramius     

http://marco-pivetta.com    



--
You received this message because you are subscribed to the Google Groups "doctrine-user" group.
To view this discussion on the web visit https://groups.google.com/d/msg/doctrine-user/-/cpq6unC51DEJ.

To post to this group, send email to doctri...@googlegroups.com.
To unsubscribe from this group, send email to doctrine-use...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/doctrine-user?hl=en.

Re: [doctrine-user] Perf issue with Inheritance (LEFT JOIN vs INNER JOIN) Eric Cambray 12/22/11 1:09 AM
If I remove condition in my select the query take much more time (46s).

Here a result without conditions with LEFT JOIN :
EXPLAIN SELECT *  FROM user_cvs cv  INNER JOIN personns p ON cv.user_id = p.id  LEFT JOIN users u ON p.id = u.id  ORDER BY u.id DESC LIMIT 21 OFFSET 1700;
+----+-------------+-------+--------+---------------+---------+---------+---------------------+--------+---------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref                 | rows   | Extra                           |
+----+-------------+-------+--------+---------------+---------+---------+---------------------+--------+---------------------------------+
|  1 | SIMPLE      | cv    | ALL    | user_id       | NULL    | NULL    | NULL                | 198990 | Using temporary; Using filesort |
|  1 | SIMPLE      | p     | eq_ref | PRIMARY       | PRIMARY | 4       |cv.user_id |      1 | Using where                     |
|  1 | SIMPLE      | u     | eq_ref | PRIMARY       | PRIMARY | 4       | p.id       |      1 |                                 |
+----+-------------+-------+--------+---------------+---------+---------+---------------------+--------+---------------------------------+

Same with INNER JOIN : (0.09s)
EXPLAIN SELECT *  FROM user_cvs cv  INNER JOIN personns p ON cv.user_id = p.id  INNER JOIN users u ON p.id = u.id  ORDER BY p.id DESC LIMIT 21 OFFSET 1700;
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref           | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
|  1 | SIMPLE      | u     | index  | PRIMARY       | PRIMARY | 4       | NULL          | 1721 |             |
|  1 | SIMPLE      | p     | eq_ref | PRIMARY       | PRIMARY | 4       | u.id |    1 |             |
|  1 | SIMPLE      | cv    | ref    | user_id       | user_id | 4       | p.id |    1 | Using where |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+

Same with LEFT JOIN and I just change the order by ID from u.id to p.id : (0.08s)
EXPLAIN SELECT *  FROM user_cvs cv  INNER JOIN personns p ON cv.user_id = p.id  INNER JOIN users u ON p.id = u.id  ORDER BY p.id DESC LIMIT 21 OFFSET 1700;
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref           | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
|  1 | SIMPLE      | p     | index  | PRIMARY       | PRIMARY | 4       | NULL          | 1721 |             |
|  1 | SIMPLE      | cv    | ref    | user_id       | user_id | 4       | p.id |    1 | Using where |
|  1 | SIMPLE      | u     | eq_ref | PRIMARY       | PRIMARY | 4       | p.id |    1 |             |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+

So it clearly the number of rows, in the original query it takes lot of rows from cv with temporary / filesort / ... but I don't undersand really why. But it's not the WHERE conditions.

Re: [doctrine-user] Perf issue with Inheritance (LEFT JOIN vs INNER JOIN) Marco Pivetta 12/22/11 1:17 AM
Well, 198990 rows using a temporary table and file sort... hehe, I can understand the poor RDBMS :D

In the first analyzed query you are sorting results by u.id (p.id would be the same!). The fact is that the LEFT JOIN produces lots of u.id = NULL results. There's no indexing on that.
Just try analyzing with ORDER BY p.id :)


Marco Pivetta

http://twitter.com/Ocramius     

http://marco-pivetta.com    



--
You received this message because you are subscribed to the Google Groups "doctrine-user" group.
To view this discussion on the web visit https://groups.google.com/d/msg/doctrine-user/-/a6S4eOFzfPkJ.

To post to this group, send email to doctri...@googlegroups.com.
To unsubscribe from this group, send email to doctrine-use...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/doctrine-user?hl=en.

Re: [doctrine-user] Perf issue with Inheritance (LEFT JOIN vs INNER JOIN) Eric Cambray 12/22/11 1:23 AM
You mean this :

EXPLAIN SELECT *  FROM user_cvs cv  INNER JOIN personns p ON cv.user_id = p.id  LEFT JOIN users u ON p.id = u.id  ORDER BY p.id DESC LIMIT 21 OFFSET 1800;
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref           | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
|  1 | SIMPLE      | p     | index  | PRIMARY       | PRIMARY | 4       | NULL          | 1821 |             |
|  1 | SIMPLE      | cv    | ref    | user_id       | user_id | 4       | dyb_prod.p.id |    1 | Using where |
|  1 | SIMPLE      | u     | eq_ref | PRIMARY       | PRIMARY | 4       | dyb_prod.p.id |    1 |             |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+


(it was my third test)
I can understand the LEFT and NULL with u.id, but why change the order do this?


2011/12/22 Marco Pivetta <ocra...@gmail.com>



--
Tel : 06-22-21-57-99
Re: [doctrine-user] Perf issue with Inheritance (LEFT JOIN vs INNER JOIN) Marco Pivetta 12/22/11 1:27 AM
Because sorting by p.id would use an index (the personns primary key), while sorting by u.id (not formally correct, but I don't have an academic education :( ) uses the result of the join, and no indexes.
Did you notice that there's no file sort or temporary table? The fact is that the MySQL optimized recognized that it could use some possible_keys to work on your resultset :)
Hope that helps... This query should now take << 1s :D


Marco Pivetta

http://twitter.com/Ocramius     

http://marco-pivetta.com    



Re: [doctrine-user] Perf issue with Inheritance (LEFT JOIN vs INNER JOIN) Eric Cambray 12/22/11 1:39 AM
OK, thanks for your help.
Is there any bug from Doctrine2 side from your point of view? (because the query is generated by Doctrine2)

Cause here, if I want to have a query < 1s (and I will), I need to use a native query, not DQL :(
Re: [doctrine-user] Perf issue with Inheritance (LEFT JOIN vs INNER JOIN) Marco Pivetta 12/22/11 1:43 AM
Could you please check again the ORDER BY in DQL and in SQL?


Marco Pivetta

http://twitter.com/Ocramius     

http://marco-pivetta.com    



--
You received this message because you are subscribed to the Google Groups "doctrine-user" group.
To view this discussion on the web visit https://groups.google.com/d/msg/doctrine-user/-/wqJcMe1oWwgJ.

To post to this group, send email to doctri...@googlegroups.com.
To unsubscribe from this group, send email to doctrine-use...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/doctrine-user?hl=en.

Re: [doctrine-user] Perf issue with Inheritance (LEFT JOIN vs INNER JOIN) Eric Cambray 12/22/11 1:56 AM
I don't understand.

My ORDER BY in DQL is "o.id" (as o is the owner of the CV, a Personn that inherit from User), I don't really see how to change something here.

2011/12/22 Marco Pivetta <ocra...@gmail.com>



--
Tel : 06-22-21-57-99
Re: [doctrine-user] Perf issue with Inheritance (LEFT JOIN vs INNER JOIN) Marco Pivetta 12/22/11 1:59 AM
The fact is that I see "ORDER BY o.id" in your DQL, while ORDER  BY "ORDER BY u.id" in your SQL. If you can confirm that, it looks like an issue (to me). You could eventually ask on IRC...

Marco Pivetta

http://twitter.com/Ocramius     

http://marco-pivetta.com    



Re: [doctrine-user] Perf issue with Inheritance (LEFT JOIN vs INNER JOIN) Marco Pivetta 12/22/11 2:49 AM
Came back to you as I completely misunderstood one of the concept you exposed, which is: "Personn that inherit from User"
I was thinking "User that inherit from Personn" all the time (asm89 made me notice that on IRC).
Okay, I don't have a fast solution here as Doctrine is actually doing right with the ORDER BY from its logic, and the LEFT JOIN is correct too.
I don't have a simple solution for your problem for now, sorry...
I suggest you to try as asm89 suggested me, by adding "AND o.id IS NOT NULL" for now


Marco Pivetta

http://twitter.com/Ocramius     

http://marco-pivetta.com    



Re: [doctrine-user] Perf issue with Inheritance (LEFT JOIN vs INNER JOIN) Eric Cambray 12/22/11 3:04 AM
Try the "AND o.id IS NOT NULL" and it works! (I don't see the asm89 message)
So thanks a lot for this workaround.


Re: [doctrine-user] Perf issue with Inheritance (LEFT JOIN vs INNER JOIN) Marco Pivetta 12/22/11 3:07 AM
Just fyi, we're trying to get to understand why this happened :)


Marco Pivetta

http://twitter.com/Ocramius     

http://marco-pivetta.com    



On 22 December 2011 12:04, Eric Cambray <bact...@gmail.com> wrote:
Try the "AND o.id IS NOT NULL" and it works! (I don't see the asm89 message)
So thanks a lot for this workaround.


--
You received this message because you are subscribed to the Google Groups "doctrine-user" group.
To view this discussion on the web visit https://groups.google.com/d/msg/doctrine-user/-/Q0IUNOz40ywJ.

To post to this group, send email to doctri...@googlegroups.com.
To unsubscribe from this group, send email to doctrine-use...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/doctrine-user?hl=en.