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* <[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 "
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
