I would try adding an index on the freetags.tag column as you are querying
against that column with
WHERE tag = 'shot'
HTH,
Dan
On 6/19/07, Kishore Jalleda <kjalleda@stripped> 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
>
