[SOLVED] mysql/php: keeping an extra record while paging

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.