List:General Discussion« Previous MessageNext Message »
From:Kishore Jalleda Date:June 19 2007 10:20pm
Subject:Slow query examining 10 Million Rows, please help !!!
View as plain text  
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

Thread
Slow query examining 10 Million Rows, please help !!!Kishore Jalleda19 Jun
  • Re: Slow query examining 10 Million Rows, please help !!!Dan Buettner20 Jun
  • Re: Slow query examining 10 Million Rows, please help !!!Brent Baisley20 Jun
    • Re: Slow query examining 10 Million Rows, please help !!!Kishore Jalleda20 Jun
      • RE: Slow query examining 10 Million Rows, please help !!!Robert DiFalco20 Jun