3 ms·
I believe they may have intended to write this query with INNER JOINs instead of LEFT JOINs. If so, the result of the query would be identical but the second o
by gtsteve 6y ago
I believe they may have intended to write this query with INNER JOINs instead of LEFT JOINs.
If so, the result of the query would be identical but the second one would likely have performance problems given the category_id filter is not being applied at the point where bar is joined. A clever database engine might find an optimisation but I wouldn't count on it.
This illustrates the subtle problem that ORMs can introduce - all logical tests pass but issues emerge over time. I found that typically every ORM based problem was resolved by rewriting it using SQL.
I used to use ORMs extensively, but my philosophy now is that you can make simple queries by using a query generator (i.e. connection.GetByID("MyTable", 100)) and complex queries have to be written by hand. I would not choose to use an ORM again.
- btilly 6y agoNo, left joins were intended here. They mean different things. With inner joins the semantics are the same. If the performance is different, the database query optimizer is broken.
- gtsteve 6y agoI see, apologies. However, I'm reasonably certain we'd see poor performance with that query on MySQL 5.7, but I don't think it's got as good an optimiser as Postgres for example.
- btilly 6y agoI can't say that MySQL doesn't perform poorly. But if so, then their optimizer is more broken than I though. Even SQLite figures this one out and does it right.