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. On Nov 30, 5:53 pm, Clifford Heath <[email protected]> wrote: > -----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. > > > > > Is it possible to remove the GROUP BY clause? > > > Thanks, > > Jon > > > On Nov 3, 2:36 pm, Gary Yngve <[email protected]> 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`, > >> `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 > > > -- > > > You received this message because you are subscribed to the Google > > Groups "DataMapper" group. > > To post to this group, send email to [email protected]. > > To unsubscribe from this group, send email to > > [email protected] > > . > > For more options, visit this group > > athttp://groups.google.com/group/datamapper?hl=en > > . > > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.4.9 (Darwin) > > iEYEARECAAYFAksUTNYACgkQROuaL1tmAEZqmgCeMH27oWFflIg6upsAtpJ+HJ5t > fiQAn1Oi2LSPYE2d66x+7U6aa0yCn7I3 > =krCm > -----END PGP SIGNATURE----- -- You received this message because you are subscribed to the Google Groups "DataMapper" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/datamapper?hl=en.
