Yes I already did try adding an index on tag, but as you said it didn't work as its using the primary key from the freetags table for the join , anyway I will try adding an index on "object_type", and see if that helps ...
Thanks Kishore Jalleda http://kjalleda.googlepages.com On 6/20/07, Brent Baisley <[EMAIL PROTECTED]> wrote:
As Dan mentioned, you're searching on the 'tag' field which has no index. But since that field is in the table you're joining on, adding an index on it might not help. You actually searching on the tag_id in the join field, not the 'tag'. Add an index on 'object_type' in the freetagged_objects table since you're searching on object_type=1. You're doing a full table scan on that table as indicated my the explain. On Jun 19, 2007, at 6:20 PM, Kishore Jalleda wrote: > 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
