sqlmysqlpagination

mysql/php: keeping an extra record while paging


I have a list of records that I want to page through using LIMIT however the first record that is returned without LIMIT is also the root identifier for the rest of the records and I need to keep it for every page. Is this possible? (I would just prefer not to run a extra sql statement)

id  |  index  |  title
1   |  0      |  index of titles
2   |  1      |  title1
3   |  1      |  title2
4   |  1      |  title3
5   |  1      |  title4

LIMIT 3, 2 should return...

id  |  index  |  title
1   |  0      |  index of titles
4   |  1      |  title3
5   |  1      |  title4

Solution

  • SELECT  *
    FROM    (
            SELECT  *
            FROM    mytable
            WHERE   index = 0
            ORDER BY
                    index, id
            LIMIT 1
            ) q
    UNION ALL
            (
            SELECT  *
            FROM    mytable
            WHERE   index = 1
            ORDER BY
                    index, id
            LIMIT 3, 2
            ) q2
    

    If you have a composite key on (index, id) (in MyISAM) or just an index on index (in InnoDB), the first query will cost almost nothing.