Why 30,000,000 rows simple table index select so slow?

this is the table:

CREATE TABLE `gossip` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`owner` int(11) NOT NULL,
`sender` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `owner_id` (`owner`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin


table status:
mysql> show table status like 'gossip'\G;
*************************** 1. row ***************************
Name: gossip
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 37101402
Avg_row_length: 39
Data_length: 1447034880
Max_data_length: 0
Index_length: 1232027648
Data_free: 0
Auto_increment: 44209650
Create_time: 2006-09-27 07:04:46
Update_time: NULL
Check_time: NULL
Collation: utf8_bin
Checksum: NULL
Create_options:
Comment: InnoDB free: 724992 kB
1 row in set (0.13 sec)

ERROR:
No query specified

query:
select lw.id, lw.sender as guest_id from gossip lw where lw.owner =
21821 order by lw.id desc limit 18540, 20;
+----------+------------+
| id | guest_id |
+----------+------------+
| 17572396 | 2011641305 |
| 17569219 | 21821 |
| 17569085 | 21821 |
| 17568968 | 21821 |
| 17568878 | 21821 |
| 17568803 | 21821 |
| 17568565 | 21821 |
| 17568222 | 21821 |
| 17568142 | 21821 |
| 17567716 | 21821 |
| 17567658 | 21821 |
| 17567542 | 21821 |
| 17546206 | 2018350180 |
| 17486767 | 31845034 |
| 17485925 | 26940439 |
| 17431019 | 31919829 |
| 17382485 | 37769 |
| 17350621 | 2011641305 |
| 17339012 | 1753713823 |
| 17331749 | 54763 |
+----------+------------+
20 rows in set (49.20 sec)


slow log:
# Query_time: 49 Lock_time: 0 Rows_sent: 20 Rows_examined: 18560
select lw.id, lw.sender as guest_id from gossip lw where lw.owner =
21821 order by lw.id desc limit 18540, 20;

Any hint will be appreciated.

I don't understand why you are using an index like `owner_id` composed by
`owner` and `id`!
I think that you should change it removing `id` from the fields list:
....
PRIMARY KEY (`id`),
KEY `owner_id` (`owner`)
....

On 10/15/06, freebat <freebat@xiaonei.com> wrote:
>
> this is the table:
>
> CREATE TABLE `gossip` (
> `id` bigint(20) unsigned NOT NULL auto_increment,
> `owner` int(11) NOT NULL,
> `sender` int(11) NOT NULL,
> PRIMARY KEY (`id`),
> KEY `owner_id` (`owner`,`id`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
>
>
> table status:
> mysql> show table status like 'gossip'\G;
> *************************** 1. row ***************************
> Name: gossip
> Engine: InnoDB
> Version: 10
> Row_format: Compact
> Rows: 37101402
> Avg_row_length: 39
> Data_length: 1447034880
> Max_data_length: 0
> Index_length: 1232027648
> Data_free: 0
> Auto_increment: 44209650
> Create_time: 2006-09-27 07:04:46
> Update_time: NULL
> Check_time: NULL
> Collation: utf8_bin
> Checksum: NULL
> Create_options:
> Comment: InnoDB free: 724992 kB
> 1 row in set (0.13 sec)
>
> ERROR:
> No query specified
>
> query:
> select lw.id, lw.sender as guest_id from gossip lw where lw.owner =
> 21821 order by lw.id desc limit 18540, 20;
> +----------+------------+
> | id | guest_id |
> +----------+------------+
> | 17572396 | 2011641305 |
> | 17569219 | 21821 |
> | 17569085 | 21821 |
> | 17568968 | 21821 |
> | 17568878 | 21821 |
> | 17568803 | 21821 |
> | 17568565 | 21821 |
> | 17568222 | 21821 |
> | 17568142 | 21821 |
> | 17567716 | 21821 |
> | 17567658 | 21821 |
> | 17567542 | 21821 |
> | 17546206 | 2018350180 |
> | 17486767 | 31845034 |
> | 17485925 | 26940439 |
> | 17431019 | 31919829 |
> | 17382485 | 37769 |
> | 17350621 | 2011641305 |
> | 17339012 | 1753713823 |
> | 17331749 | 54763 |
> +----------+------------+
> 20 rows in set (49.20 sec)
>
>
> slow log:
> # Query_time: 49 Lock_time: 0 Rows_sent: 20 Rows_examined: 18560
> select lw.id, lw.sender as guest_id from gossip lw where lw.owner =
> 21821 order by lw.id desc limit 18540, 20;
>
> Any hint will be appreciated.
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=shen139@gmail.com
>
>


--
http://www.openwebspider.org
http://www.eviltime.com

-

" Time is what we want most, but what we use worst "

On 10/15/06, freebat <freebat@xiaonei.com> wrote:
>
> This approach will make the query filesort.....


why?
You use the index owner_id to filter the rows and the primary key index to
perform the sort!
or not?
test it with explain:
explain select lw.id , lw.sender as guest_id from gossip lw where
lw.owner= 21821 order by
lw.id desc limit 18540, 20;

Shen139 wrote:
> > I don't understand why you are using an index like `owner_id` composed
> > by `owner` and `id`!
> > I think that you should change it removing `id` from the fields list:
> > ...
> > PRIMARY KEY (`id`),
> > KEY `owner_id` (`owner`)
> > ...
> >
> > On 10/15/06, *freebat* <freebat@xiaonei.com
> > <mailto:freebat@xiaonei.com>> wrote:
> >
> > this is the table:
> >
> > CREATE TABLE `gossip` (
> > `id` bigint(20) unsigned NOT NULL auto_increment,
> > `owner` int(11) NOT NULL,
> > `sender` int(11) NOT NULL,
> > PRIMARY KEY (`id`),
> > KEY `owner_id` (`owner`,`id`)
> > ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
> >
> >
> > table status:
> > mysql> show table status like 'gossip'\G;
> > *************************** 1. row ***************************
> > Name: gossip
> > Engine: InnoDB
> > Version: 10
> > Row_format: Compact
> > Rows: 37101402
> > Avg_row_length: 39
> > Data_length: 1447034880
> > Max_data_length: 0
> > Index_length: 1232027648
> > Data_free: 0
> > Auto_increment: 44209650
> > Create_time: 2006-09-27 07:04:46
> > Update_time: NULL
> > Check_time: NULL
> > Collation: utf8_bin
> > Checksum: NULL
> > Create_options:
> > Comment: InnoDB free: 724992 kB
> > 1 row in set (0.13 sec)
> >
> > ERROR:
> > No query specified
> >
> > query:
> > select lw.id <http://lw.id>, lw.sender as guest_id from gossip lw
> > where lw.owner =
> > 21821 order by lw.id <http://lw.id> desc limit 18540, 20;
> > +----------+------------+
> > | id | guest_id |
> > +----------+------------+
> > | 17572396 | 2011641305 |
> > | 17569219 | 21821 |
> > | 17569085 | 21821 |
> > | 17568968 | 21821 |
> > | 17568878 | 21821 |
> > | 17568803 | 21821 |
> > | 17568565 | 21821 |
> > | 17568222 | 21821 |
> > | 17568142 | 21821 |
> > | 17567716 | 21821 |
> > | 17567658 | 21821 |
> > | 17567542 | 21821 |
> > | 17546206 | 2018350180 |
> > | 17486767 | 31845034 |
> > | 17485925 | 26940439 |
> > | 17431019 | 31919829 |
> > | 17382485 | 37769 |
> > | 17350621 | 2011641305 |
> > | 17339012 | 1753713823 |
> > | 17331749 | 54763 |
> > +----------+------------+
> > 20 rows in set (49.20 sec)
> >
> >
> > slow log:
> > # Query_time: 49 Lock_time: 0 Rows_sent: 20 Rows_examined: 18560
> > select lw.id <http://lw.id> , lw.sender as guest_id from gossip lw
> > where lw.owner =
> > 21821 order by lw.id <http://lw.id> desc limit 18540, 20;
> >
> > Any hint will be appreciated.
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> http://lists.mysql.com/mysql?unsub=shen139@gmail.com
> >
> >
> >
> >
> > --
> > http://www.openwebspider.org
> > http://www.eviltime.com
> >
> > -
> >
> > " Time is what we want most, but what we use worst "
>



--
http://www.openwebspider.org
http://www.eviltime.com

-

" Time is what we want most, but what we use worst "

This approach will make the query filesort.....

Shen139 wrote:
> I don't understand why you are using an index like `owner_id` composed
> by `owner` and `id`!
> I think that you should change it removing `id` from the fields list:
> ...
> PRIMARY KEY (`id`),
> KEY `owner_id` (`owner`)
> ...
>
> On 10/15/06, *freebat* <freebat@xiaonei.com
> <mailto:freebat@xiaonei.com>> wrote:
>
> this is the table:
>
> CREATE TABLE `gossip` (
> `id` bigint(20) unsigned NOT NULL auto_increment,
> `owner` int(11) NOT NULL,
> `sender` int(11) NOT NULL,
> PRIMARY KEY (`id`),
> KEY `owner_id` (`owner`,`id`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
>
>
> table status:
> mysql> show table status like 'gossip'\G;
> *************************** 1. row ***************************
> Name: gossip
> Engine: InnoDB
> Version: 10
> Row_format: Compact
> Rows: 37101402
> Avg_row_length: 39
> Data_length: 1447034880
> Max_data_length: 0
> Index_length: 1232027648
> Data_free: 0
> Auto_increment: 44209650
> Create_time: 2006-09-27 07:04:46
> Update_time: NULL
> Check_time: NULL
> Collation: utf8_bin
> Checksum: NULL
> Create_options:
> Comment: InnoDB free: 724992 kB
> 1 row in set (0.13 sec)
>
> ERROR:
> No query specified
>
> query:
> select lw.id <http://lw.id>, lw.sender as guest_id from gossip lw
> where lw.owner =
> 21821 order by lw.id <http://lw.id> desc limit 18540, 20;
> +----------+------------+
> | id | guest_id |
> +----------+------------+
> | 17572396 | 2011641305 |
> | 17569219 | 21821 |
> | 17569085 | 21821 |
> | 17568968 | 21821 |
> | 17568878 | 21821 |
> | 17568803 | 21821 |
> | 17568565 | 21821 |
> | 17568222 | 21821 |
> | 17568142 | 21821 |
> | 17567716 | 21821 |
> | 17567658 | 21821 |
> | 17567542 | 21821 |
> | 17546206 | 2018350180 |
> | 17486767 | 31845034 |
> | 17485925 | 26940439 |
> | 17431019 | 31919829 |
> | 17382485 | 37769 |
> | 17350621 | 2011641305 |
> | 17339012 | 1753713823 |
> | 17331749 | 54763 |
> +----------+------------+
> 20 rows in set (49.20 sec)
>
>
> slow log:
> # Query_time: 49 Lock_time: 0 Rows_sent: 20 Rows_examined: 18560
> select lw.id <http://lw.id> , lw.sender as guest_id from gossip lw
> where lw.owner =
> 21821 order by lw.id <http://lw.id> desc limit 18540, 20;
>
> Any hint will be appreciated.
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=shen139@gmail.com
>
>
>
>
> --
> http://www.openwebspider.org
> http://www.eviltime.com
>
> -
>
> " Time is what we want most, but what we use worst "

On 10/15/06, Freebat Wangh <wanghuiwen@tsinghua.org.cn> wrote:
>
> mysql> explain select lw.id, lw.sender as guest_id from gossip lw where
> lw.owner = 21821 order by lw.id desc limit 18540, 20\G;
> *************************** 1. row ***************************
> id: 1
> select_type: SIMPLE
> table: lw
> type: ref
> possible_keys: owner_id
> key: owner_id
> key_len: 4
> ref: const
> rows: 10646
> Extra: Using where
> 1 row in set (0.03 sec)


it should be OK!
In fact when mysql uses filesort the column Extra is something like: "Using
where; Using filesort"
you can do this additional test:
explain select lw.id, lw.sender as guest_id from gossip lw where
lw.owner = 21821 order by lw.sender desc limit 18540, 20;

this query will use filesort!

--
http://www.openwebspider.org
http://www.eviltime.com

-

" Time is what we want most, but what we use worst "

At 11:55 AM 10/15/2006, you wrote:
>On 10/15/06, Freebat Wangh <wanghuiwen@tsinghua.org.cn> wrote:
>>
>>mysql> explain select lw.id, lw.sender as guest_id from gossip lw where
>>lw.owner = 21821 order by lw.id desc limit 18540, 20\G;
>>*************************** 1. row ***************************
>> id: 1
>> select_type: SIMPLE
>> table: lw
>> type: ref
>>possible_keys: owner_id
>> key: owner_id
>> key_len: 4
>> ref: const
>> rows: 10646
>> Extra: Using where
>>1 row in set (0.03 sec)
>
>
>it should be OK!
>In fact when mysql uses filesort the column Extra is something like: "Using
>where; Using filesort"
>you can do this additional test:
>explain select lw.id, lw.sender as guest_id from gossip lw where
>lw.owner = 21821 order by lw.sender desc limit 18540, 20;
>
>this query will use filesort!


I've found queries will almost always use filesorts. Try it without the
Order By clause and see if it is any faster (I'm betting it is roughly the
same speed). Keep in mind you're using an Offset in your limit clause so
MySQL will have to physically scan over those rows each time the query is
called.

Mike