last row index lost

[Linda]

Hello all,


I am using mysql version 5.0.18

I have a table with 200,000 rows with indexes. Every time I will
select 1000 rows for display, they are very fast (return within 1 sec) after successfully
preloading indexes into default cache. However, the problem is when I want to load the
very last row data, it becomes very slow (need about 1 minute).

Here is the table:
person(
id int,
name varchar(40), (indexed)
pid int, (indexed)
sid int, (indexed)
creationtime timestamp
)



-- this is fast:
mysql> select * from mytable order by name limit 199000, 999;
(999 rows in set (0.1 sec)
The EXPLAIN statement shows it is using index.


-- this is very slow when I reach the very end of data:
mysql> select * from mytable order by name limit 199000, 1000;
(1000 rows in set (55 sec)
The EXPLAIN statement shows it is using filesort instead of the index.



-- but the same query will become fast if I use "force index"
mysql> select * from mytable force index (i_name) order by name limit 199000, 1000;
(1000 rows in set (0.1 sec)


I thought that mysql would always use index if it is there and available.
How can I config mysql so that it will always use index?

Thanks in advance!

Linda