On Wed, Mar 10, 2010 at 4:01 PM, Jocelyn Fournier <[email protected]> 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 [email protected] _______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
