Re: Query that returns indexed fields involves FILESORT, why?

[Rick James]

Before you fall into the usual trap of poor performance by pagination via LIMIT, please read some of these (especially my responses):

Especially...
http://forums.mysql.com/read.php?20,286632 (How do deletes and updates affect fetching of rows?)
http://forums.mysql.com/read.php?24,302536 (Slow inner join help!)
http://forums.mysql.com/read.php?115,260632 (3 Table Join (indexed) results in where-temp-filesort)
http://forums.mysql.com/read.php?10,239876,239913
http://forums.mysql.com/read.php?24,291646 (Very Slow Simple Query)

Also...
http://forums.mysql.com/read.php?26,355015 (Replication Delays in MySQL 5.1)
http://forums.mysql.com/read.php?24,291131 (Sorting with limit across a join. How to make it fast?)
http://forums.mysql.com/read.php?125,287643 (Data Modelling for an email table)
http://forums.mysql.com/read.php?24,286146 (Very high CPU utilisation with 40-50 concurrent connection on Innodb table)
http://forums.mysql.com/read.php?10,283204 (Best practices for multiple joins?)
http://forums.mysql.com/read.php?10,280159 (Load Balancing Database Server)
http://forums.mysql.com/read.php?10,280358 (Paged reading -- how? (LIMIT is slow))
http://forums.mysql.com/read.php?24,261069 (MySql 4.1 optimization search poor performance)

Some discussion of pitfalls of SQL_CALC_FOUND_ROWS:
http://forums.mysql.com/read.php?24,240838,241970
http://forums.mysql.com/read.php?52,300253 (Query Pagination with LIMIT and total results)
http://forums.mysql.com/read.php?115,295760,296739
http://forums.mysql.com/read.php?20,269950,270146