> 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
<cut> 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
