nested/assoc queries use group by?

[Gary Yngve]

A Recruiter belongs to a Team.

Suppose I do

Recruiter.first(:team_id => 'foo')

That generates a simple query:
~ (0.002456) SELECT `id`, `created_at`, `updated_at`, ... `team_id`
FROM `recruiters` WHERE `team_id` = 'foo' ORDER BY `id` LIMIT 1

Now suppose I do:
Recruiter.first('team.id' => 'foo')

That generates:
~ (0.008210) SELECT `recruiters`.`id`, `recruiters`.`created_at`,
`recruiters`.`updated_at`, ... `recruiters`.`team_id` FROM
`recruiters` INNER JOIN `teams` ON `recruiters`.`team_id` =
`teams`.`id` WHERE `teams`.`id` = '554' GROUP BY `recruiters`.`id`,
`recruiters`.`created_at`, `recruiters`.`updated_at`, ...
`recruiters`.`team_id` ORDER BY `recruiters`.`id` LIMIT 1

What I don't understand is why doesn't the second query just do:

~ (0.008210) SELECT `recruiters`.`id`, `recruiters`.`created_at`,
`recruiters`.`updated_at`, ... `recruiters`.`team_id` FROM
`recruiters` INNER JOIN `teams` ON `recruiters`.`team_id` =
`teams`.`id` WHERE `teams`.`id` = '554' ORDER BY `recruiters`.`id`
LIMIT 1

?

For that matter, are there any underlying efficiency concerns in the
DB regarding the superfluous group_by?

Thanks,
Gary

[whoismario]

I'd like back up Gary's concern on this.

A finder through association query on my server (using 0.9.11) does
not include the GROUP BY clause and takes less than half a second
while on my development machine (using 0.10.1) the same query takes
over 10 seconds.

Is it possible to remove the GROUP BY clause?

Thanks,
Jon

On Nov 3, 2:36 pm, Gary Yngve <gary.yn...@gmail.com> wrote:
> A Recruiter belongs to a Team.
>
> Suppose I do
>
> Recruiter.first(:team_id => 'foo')
>
> That generates a simple query:
> ~ (0.002456) SELECT `id`, `created_at`, `updated_at`, ... `team_id`
> FROM `recruiters` WHERE `team_id` = 'foo' ORDER BY `id` LIMIT 1
>
> Now suppose I do:
> Recruiter.first('team.id' => 'foo')
>
> That generates:
> ~ (0.008210) SELECT `recruiters`.`id`, `recruiters`.`created_at`,
> `recruiters`.`updated_at`, ... `recruiters`.`team_id` FROM
> `recruiters` INNER JOIN `teams` ON `recruiters`.`team_id` =

> `teams`.`id` WHERE `teams`.`id` = '554'GROUPBY `recruiters`.`id`,

[Clifford Heath]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 01/12/2009, at 9:43 AM, whoismario wrote:
> I'd like back up Gary's concern on this.
> A finder through association query on my server (using 0.9.11) does
> not include the GROUP BY clause and takes less than half a second
> while on my development machine (using 0.10.1) the same query takes
> over 10 seconds.

I'd bet you're missing a required index. And a DBMS with a proper
optimiser.

Is recruiters.team_id indexed? It needs to be.

Clifford Heath.

> --
>
> You received this message because you are subscribed to the Google
> Groups "DataMapper" group.
> To post to this group, send email to datam...@googlegroups.com.
> To unsubscribe from this group, send email to datamapper+...@googlegroups.com
> .
> For more options, visit this group at http://groups.google.com/group/datamapper?hl=en
> .
>
>

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (Darwin)

iEYEARECAAYFAksUTNYACgkQROuaL1tmAEZqmgCeMH27oWFflIg6upsAtpJ+HJ5t
fiQAn1Oi2LSPYE2d66x+7U6aa0yCn7I3
=krCm
-----END PGP SIGNATURE-----

[whoismario]

My tables are different, but I did already add an index to the *_id
field and it slowed the query down further.

The real issue is that, as Gary said, it's creating a superfluous
GROUP BY statement since it's including every field in the table and
not using an aggregate function.

The DBMS is MySQL. I don't know if it has a good optimizer or not,
but it's probably the DBMS of choice for many DM users.

> > For more options, visit this group athttp://groups.google.com/group/datamapper?hl=en

[Clifford Heath]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 01/12/2009, at 10:15 AM, whoismario wrote:
> The DBMS is MySQL. I don't know if it has a good optimizer or not,
> but it's probably the DBMS of choice for many DM users.

Unfortunately true. Its optimiser technology is worse than what the
commercial DBMSs abandoned in 1985, and Oracle will ensure
that it never gets close to threatening the enterprise products.

Clifford Heath.

> For more options, visit this group at http://groups.google.com/group/datamapper?hl=en

> .
>
>

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (Darwin)

iEYEARECAAYFAksUVfYACgkQROuaL1tmAEaE3wCbBfzrV8+0GtFrFAcQigZR1xX0
xxQAn0Pa8ppT/cIjXS/VpattXTDrqrgs
=7T0W
-----END PGP SIGNATURE-----