I don't think I'd seriously consider the max() option, especially if you're expecting the table to grow large. Using a table to store the next record number (your last_used_record_nums table) is a technique that is fairly commonly used. You can increment and retrieve the value atomically if you use the LAST_INSERT_ID():
insert into last_used_record_nums(routerid, recordnum) values (####, last_insert_id(1)) on duplicate key update recordnum = last_insert_id(recordnum + 1); then select last_insert_id(); to get the incremented value. You could probably wrap this in an INSERT trigger on your stats table so your application wouldn't need to worry about it. -Travis -----Original Message----- From: Jake Peavy [mailto:djstu...@gmail.com] Sent: Tuesday, October 05, 2010 10:34 AM To: MySQL General Mailing List Subject: Re: Add "record number" to timestamped router data to facilitate cross join On Fri, Oct 1, 2010 at 12:24 PM, Jake Peavy <djstu...@gmail.com> wrote: > All, > > I have a number of routers which report in with various stats > periodicially. This period is not a regular interval and can drift based on > other factors. Each router drifts independently. The stats the routers > provide need to be analyzed in terms of deltas between reports (rather than > the absolute number). Therefore I need to perform a cross join to compare > the rows for a given routerID (something like SELECT r1.timestamp, > r2.counter1-r1.counter1 FROM router_data as r1 JOIN router_data as r2 ON > (r2.recordNum = r1.recordNum + 1)) > > Here's an example of some raw data to give you an idea showing 3 records > each from 2 devices: > > +----------+------------------+----------+----------+----------+ > | routerID | timestamp | counter1 | counter2 | counter3 | > +----------+------------------+----------+----------+----------+ > | 1 | 24/08/2010 10:36 | 40 | 55 | 70 | > | 2 | 24/08/2010 10:51 | 31 | 79 | 29 | > | 2 | 24/08/2010 12:19 | 94 | 61 | 64 | > | 1 | 24/08/2010 12:41 | 4 | 84 | 82 | > | 1 | 24/08/2010 14:58 | 26 | 9 | 62 | > | 2 | 24/08/2010 14:51 | 36 | 75 | 31 | > +----------+------------------+----------+----------+----------+ > > My plan, to facilitate the cross join, was to add a per-device "record > number" like follows: > > > +----------+------------------+-----------+----------+----------+----------+ > | routerID | timestamp | recordNum | counter1 | counter2 | counter3 > | > > +----------+------------------+-----------+----------+----------+----------+ > | 1 | 24/08/2010 10:36 | 1 | 40 | 55 | 70 > | > | 2 | 24/08/2010 10:51 | 1 | 31 | 79 | 29 > | > | 2 | 24/08/2010 12:19 | 2 | 94 | 61 | 64 > | > | 1 | 24/08/2010 12:41 | 2 | 4 | 84 | 82 > | > | 1 | 24/08/2010 14:58 | 3 | 26 | 9 | 62 > | > | 2 | 24/08/2010 14:51 | 3 | 36 | 75 | 31 > | > > +----------+------------------+-----------+----------+----------+----------+ > > So here's my question, first, of course, is there a better way to perform > the cross join? If not, what's the easiest way to add and increment the > recordNum field? Can I do it directly in SQL somehow? Or do I need to do > it in my parser? If I do it in my parser, it runs periodically (as it > receives the reports) so I think it would need to figure out what record > number it assigned to which device last so it would know where to restart > the numbering. Should I hold that in the parser itself, or a separate table > (SELECT routerID,last_used_record_num FROM last_used_record_nums; then > parse, incrementing record num, then write the last ones back to that table) > or by querying the main table itself (SELECT routerID, MAX(recordNum) FROM > router_data GROUP BY routerID)? My only concern with the last approach is > that router_data is going to get very large and that query may get very > slow. > > TIA for any advice, > Hey all, Anyone have any thoughts/advice on the best way to manage this "record number"? What about a stored procedure? Or use of variables? TIA -- -jp If you're traveling in a time machine, and you're eating corn on the cob, I don't think it's going to affect things one way or the other. But here's the point I'm trying to make: Corn on the cob is good, isn't it? deepthoughtsbyjackhandey.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
