On 10/15/06, freebat <[EMAIL PROTECTED]> 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* <[EMAIL PROTECTED] > <mailto:[EMAIL PROTECTED]>> 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/[EMAIL PROTECTED] > > > > > -- > 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 "
