← Back to team overview

maria-developers team mailing list archive

Re: LIMIT optimisations


Hi Mark !

Unfortunately, if you want to have the ability to jump to random page, you're stuck and cannot use this workaround ! And in the case of a forum, you're most of the time jumping to the latest page of a topic. Anyway I'm pretty sure we could add some optimisations to improve the current behaviour (like ICP), if it's not already the case (I've not checked this with MySQL 5.5 / Maria yet)


Le 11/03/10 01:35, MARK CALLAGHAN a écrit :
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.