Some great stuff on getting the best performance out of MySQL …
MySQL Performance Blog
I have just been reading this …
ORDER BY … LIMIT Performance Optimization. Pagination is used on just about every site I work on – that article is great on speeding up queries using ORDER BY and LIMIT.