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
This is the query:
INSERT INTO document_totals_pm
(collection_id, tcml_field_id, value, value_count)
SELECT collection_id, tcml_field_id, value, COUNT(*)
FROM document_data_pm
GROUP BY collection_id, tcml_field_id, value
And here is the output of a EXPLAIN on the SELECT part of
the query, showing the the index isn't being used:
mysql> explain SELECT collection_id, tcml_field_id, value, COUNT(*) from
document_data_pm
-> GROUP BY collection_id, tcml_field_id, value;
+------------------+------+---------------+------+---------+------+----------+-----------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra
| |
+------------------+------+---------------+------+---------+------+----------+-----------------+
| document_data_pm | ALL | NULL | NULL | NULL | NULL | 28874247 | Using
|temporary |
+------------------+------+---------------+------+---------+------+----------+-----------------+
1 row in set (1 min 9.70 sec)
--
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
