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

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