Hi everybody,
we have this super slow query which is going through more
than 10 million rows to retrieve results, here is the query and other
information, I tried a few things to make this faster , but failed , so any
help from you guys in making this faster is greatly appreciated ....
# Query_time: 10 Lock_time: 0 Rows_sent: 1 Rows_examined: 11863498
SELECT DISTINCT object_id
FROM freetagged_objects INNER JOIN freetags ON (tag_id = id)
WHERE tag = 'shot'
AND object_type = 1
ORDER BY object_id ASC
LIMIT 0, 10
explain gives the following output
+----+-------------+--------------------+--------+---------------+---------+---------+--------------------------------------+---------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows |
Extra |
+----+-------------+--------------------+--------+---------------+---------+---------+--------------------------------------+---------+----------------------------------------------+
| 1 | SIMPLE | freetagged_objects | ALL | PRIMARY | NULL
| NULL | NULL | 9079381 | Using where;
Using temporary; Using filesort |
| 1 | SIMPLE | freetags | eq_ref | PRIMARY | PRIMARY
| 4 | osCommerce.freetagged_objects.tag_id | 1 | Using where;
Distinct |
+----+-------------+--------------------+--------+---------------+---------+---------+--------------------------------------+---------+----------------------------------------------+
mysql> show create table freetagged_objects;
| freetagged_objects | CREATE TABLE `freetagged_objects` (
`tag_id` int(11) unsigned NOT NULL default '0',
`tagger_id` int(11) unsigned NOT NULL default '0',
`object_id` int(11) unsigned NOT NULL default '0',
`tagged_on` datetime NOT NULL default '0000-00-00 00:00:00',
`object_type` int(11) NOT NULL default '0',
PRIMARY KEY (`tag_id`,`tagger_id`,`object_id`),
KEY `tagger_id_index` (`tagger_id`),
KEY `object_id_tagger_id_index` (`object_id`,`tagger_id`),
KEY `object_id_tag_id_index` (`object_id`,`tag_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
mysql> show create table freetags;
| freetags | CREATE TABLE `freetags` (
`id` int(11) unsigned NOT NULL auto_increment,
`tag` varchar(30) NOT NULL default '',
`raw_tag` varchar(50) NOT NULL default '',
`suppress` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `raw_tag` (`raw_tag`(10))
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
Freetags table has like a million rows in it ....
MySQL version 4.1.11 , server has 16GB RAM ......
Kishore Jalleda
http://kjalleda.googlepages.com/mysqlprojects
