RE: Index not used with GROUP BY? (bug?)
On 15 Nov 2001, at 22:32, Carsten H. Pedersen wrote: > > Thanks for the response, but I think you missed the key > > collection_field_value right under the primary key above. > > You're right -- Sorry. Have you tried COUNT'ing something > other than *? i.e. document_id or resource_id? I tried it. It makes no difference. This seems to be a bug, or there's something wrong with my understanding of the documentation. I have the query SELECT collection_id, tcml_field_id, value, COUNT(*) FROM document_data_pm GROUP BY collection_id, tcml_field_id, value where the document_data_pm table has the index KEY `collection_field_value` (`collection_id`,`tcml_field_id`,`value`(12)) What would prevent MySQL from using the index? By the way, I'm using version 3.23.44 on Red Hat Linux. -- Keith C. Ivey <[EMAIL PROTECTED]> Washington, DC - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Index not used with GROUP BY?
> On 15 Nov 2001, at 21:59, Carsten H. Pedersen wrote: > > > > PRIMARY KEY > > > (`collection_id`,`document_id`,`tcml_field_id`,`order_number`), > > > KEY `collection_field_value` > > > (`collection_id`,`tcml_field_id`,`value`(12)), > > > KEY `value` (`value`(12)), > > > KEY `resource_id` (`resource_id`) > > > ) TYPE=MyISAM > > > > > > > > MySQL can't make use of the PRIMARY KEY -- as the document_id > > part breaks any chance MySQL has of using the indexed > > collection_id-tcml_field_id relation. Try either adding a > > new index on just collection_id, tcml_field_id (and maybe > > value), or simply re-define your PRIMARY KEY so that those > > two fields make up the first part of the key. > > Thanks for the response, but I think you missed the key > collection_field_value right under the primary key above. You're right -- Sorry. Have you tried COUNT'ing something other than *? i.e. document_id or resource_id? > Keith C. Ivey <[EMAIL PROTECTED]> / Carsten - smokin' 'em while he's still got 'em :-) -- Carsten H. Pedersen keeper and maintainer of the bitbybit.dk MySQL FAQ http://www.bitbybit.dk/mysqlfaq - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Index not used with GROUP BY?
On 15 Nov 2001, at 21:59, Carsten H. Pedersen wrote: > > PRIMARY KEY > > (`collection_id`,`document_id`,`tcml_field_id`,`order_number`), > > KEY `collection_field_value` > > (`collection_id`,`tcml_field_id`,`value`(12)), > > KEY `value` (`value`(12)), > > KEY `resource_id` (`resource_id`) > > ) TYPE=MyISAM > > > > MySQL can't make use of the PRIMARY KEY -- as the document_id > part breaks any chance MySQL has of using the indexed > collection_id-tcml_field_id relation. Try either adding a > new index on just collection_id, tcml_field_id (and maybe > value), or simply re-define your PRIMARY KEY so that those > two fields make up the first part of the key. Thanks for the response, but I think you missed the key collection_field_value right under the primary key above. That's what you're suggesting adding, but I've already got it. -- Keith C. Ivey <[EMAIL PROTECTED]> Washington, DC - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Index not used with GROUP BY?
> I'm trying to keep a table of summary statistics for another table, > because I expected that generating the counts would take a while. > But I never expected the query to take hours, as it does. On > examination, I see that the query is not using an index, even > though the table has an index on the three fields being used in > the GROUP BY. The "How MySQL Uses Indexes" section of the > documentation seems to say that the index should be used in that > case. > > I'd appreciate any hints anyone could give me about what the > problem might be and how I can get this query to run in a > reasonable time. > > The table has more than 28 million rows and looks like this: > > CREATE TABLE `document_data_pm` ( > `collection_id` smallint(5) unsigned NOT NULL default '0', > `document_id` int(10) unsigned NOT NULL default '0', > `tcml_field_id` tinyint(3) unsigned NOT NULL default '0', > `order_number` tinyint(3) unsigned NOT NULL default '0', > `value` varchar(255) NOT NULL default '', > `resource_id` int(10) unsigned NOT NULL default '0', > PRIMARY KEY > (`collection_id`,`document_id`,`tcml_field_id`,`order_number`), > KEY `collection_field_value` > (`collection_id`,`tcml_field_id`,`value`(12)), > KEY `value` (`value`(12)), > KEY `resource_id` (`resource_id`) > ) TYPE=MyISAM MySQL can't make use of the PRIMARY KEY -- as the document_id part breaks any chance MySQL has of using the indexed collection_id-tcml_field_id relation. Try either adding a new index on just collection_id, tcml_field_id (and maybe value), or simply re-define your PRIMARY KEY so that those two fields make up the first part of the key. / Carsten -- Carsten H. Pedersen keeper and maintainer of the bitbybit.dk MySQL FAQ http://www.bitbybit.dk/mysqlfaq - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
