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]
