maria-developers team mailing list archive

[MARK CALLAGHAN <mdcallag@xxxxxxxxx>]

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.

-- 
Mark Callaghan
mdcallag@xxxxxxxxx