
HATBM uses group by & order by by default unnecessarily, which is very bad for performance
Reported by Kevin Watt | June 30th, 2010 @ 06:40 PM
Groups HABTM Users. Calling group.users loads this SQL:
~ (0.000226) SELECT id
, title
,
group_apps
FROM groups
WHERE
id
= 1 ORDER BY id
LIMIT 1
~ (0.000272) SELECT users
.id
FROM
users
INNER JOIN groups_users
ON
users
.id
=
groups_users
.user_id
INNER JOIN
groups
ON
groups_users
.group_id
=
groups
.id
WHERE
groups_users
.group_id
= 1 GROUP BY
users
.id
ORDER BY
users
.id
The Group by / Order by causes this in the explain for the
query:
Using index; Using temporary; Using filesort. Which is very slow
for large result sets.
I'd be interested in looking into this myself, but I can't figure out where the group by / order by are being loaded from, as searching the dm-core source for it doesn't return anything relevant.
No comments found
Please Sign in or create a free account to add a new ticket.
With your very own profile, you can contribute to projects, track your activity, watch tickets, receive and update tickets through your email and much more.
Create your profile
Help contribute to this project by taking a few moments to create your personal profile. Create your profile »