On Wed, Mar 10, 2010 at 4:01 PM, Jocelyn Fournier <joce@doctissimo.fr> wrote:
Hi !
On a bulletin board context, that's quite simple :
Let's say we want to display a forum thread containing a lot of posts.
To simplify, I have the following table 'posts' which contains :
- id_thread - id_post - content - id_author
If I want to display a paginated posts list of a given topic, with 30 posts per page, I have to do :
SELECT content, author_name FROM posts LEFT JOIN author USING (id_author) ....... WHERE id_thread=.... ORDER BY id_post ASC LIMIT x,30 I have a PK on (id_thread, id_post).
If I have a lot of posts in this thread, I could have easily a big LIMIT to get the last pages of the thread, which are the more often read (and the query will be triggered quite often especially if google like my bulletin board :)). The current behaviour of MyISAM seems to be to always scan all the rows; than means if I have a LIMIT 12000,40, the first useless 12000 rows will be scanned, and this is especially bad if "content" is a TEXT field (no static lengths row here).
This is the behavior for all storage engines. I don't think you are going to get the optimization in MySQL that I think you are asking for. I have written about the performance problems of pagination and a workaround in http://www.facebook.com/note.php?note_id=206034210932. -- Mark Callaghan mdcallag@gmail.com