I have a result set from MYSQL which I'm displaying in a paging scenario using PHP. (Prev/Next links)
Many result rows may have "child" rows associated with it. IE, they share a column containing the same "root number".
Due to the paging and limit arguments in my query, those groups of rows with common root numbers can be split between pages, which makes the display awkward.
I need the query to take that root number column into consideration and NOT split those child rows across to a second page. Instead, it should go ahead and include all of the rows sharing that root number on the same page together. In my mind, to achieve this, the query would take the root number into account and adjust the LIMIT upwards if the last row in the select has other rows with the same root number.
Seems like the offset value could also be exploited to achieve the desired result, but I'm not sure how I might do that on the fly.
Does anyone have thoughts on how to accomplish this?
SELECT * FROM (`tablename`) LIMIT 3600, 100
Example data:
id name rootnumber
-------------------------------------------------
1 Joe 789
2 Susan 789
3 Bill 789
4 Peter 123
Pagination with limit has several problems. Normally you count the complete result set (which takes almost as much work for the MySQL-server as retrieving the whole set), and as soon as you have limit 2000,50, you put as much work on the server as retrieving the first 2050 rows and throwing the first 2000 away. The third problem is that there is no other solution as easy as limit. ;-)
So, you could try different things:
Send bigger data packets of many pages to the client and do pagination in html/javascript/css. Just fetch a new packet when the user comes to the last of those pages. There you can work with the trick of fetching one row more than needed, so you see if that row is the same rootnumber as the last (so you discard that rootnumber completely) or if it has a new rootnumber (so the last rootnumber was completely read)
Give the user better search parameters - no user really reads through 250 lines completely, the user normally just searches for a certain date or a certain keyword, or some property of the root. As soon as the user 'paginates' through months or weeks, she has a clue at what time it was. This does have the problem of sometimes very different 'page' sizes. But that you could fix that in the client.
The MySQL-Server is very happy to do the search like where date between '2013-12-01' and '2014-01-01' or where color='blue' and customer.sex='f', there it an work with its magic and indices. Much better than that `limit 2000, 50.
This is work, this is not easy, but if you are good you can find better solutions for the customer, who does not really like to read all the lines in between.
EDIT: There are technical solutions to that. That you show entries together when they have the same root number looks like you sort them. So in a query before (we do hope your MySQL Server has a Sado touch and likes that) and fetch only the root numbers:
select t.* from tablename as t
inner join
(
select rootnumber from tablename limit 3600, 50 # you put in your sort her, do you?
) as mt on mt.rootnumber = t.rootnumber;
As soon as your MySQL Server version uses indices on where in (subquery) (try explain), you can also use the nicer version
/* TRY EXPLAIN AND BEWARE OF FULL TABLE SCAN!*/
select t.* from table_name where rootnumber in
( select rootnumber from table_name limit 3600, 50)
;
But right now that might be really slow.
But: try to provide search parameters to reduce the table walking to an absolute minimum!
Much Fun!