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 "