Could also be the DISTINCT processing depending on the number of dups and the fields in the result set that must be sorted to perform the distinct operation. Normally if there were a lot of dupes I would suggest a sub-query but that is not a great option for MySQL.
-----Original Message----- From: Kishore Jalleda [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 20, 2007 10:23 AM To: Brent Baisley Cc: mysql@lists.mysql.com Subject: Re: Slow query examining 10 Million Rows, please help !!! 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 > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
