On Wed, Mar 10, 2010 at 4:01 PM, Jocelyn Fournier<joce@xxxxxxxxxxxxx> 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.
