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
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.