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