-
-
Save si/1868882 to your computer and use it in GitHub Desktop.
I have a table of petrol purchases, each recording the current odometer reading: | |
id | odometer | user_id | created | |
------------------------------- | |
2 | 63446 | 1 | 2012-01-15 11:27:57 | |
4 | 63835 | 1 | 2012-01-19 17:09:37 | |
5 | 64145 | 1 | 2012-01-28 13:02:08 | |
I want to return each row along with a distance and days lasted for each record (comparing the current record with the next record). |
Nice!
I'm not sure it needs to be a subquery since it just reverses the set and causes 2 filesorts instead of one - and filesorts can be really slow on bigger datasets in MySQL.
Though that could just be me prematurely optimizing it.
It only uses a subquery to sort, I don't think you'd be able to do it without doing it in descending order. You could always iterate backwards through the resultset in the programming language of your choice, but you wouldn't be doing it with a query like this anyway if your app was going to be especially read heavy (you'd have denormalised it to pre-compute the difference at insert time, or you'd have thrown all manner of varniredimongos at it first).
Yeah I meant iterating over backwards. Though I would have probably just done it client side or even just put all the user's entries in one mongo document (since I'm learning that at the moment) and it seems like that would be easier to maintain and scale. Not that any of this matters unless it gets a lot of users.
Thanks for both your feedback on this. I'm conscious of performance and optimisation but realise, at this stage, it's not crucial.
Out of curiosity, what would you negate as large volumes: 10k, 100k or more records?
Pretty much the point the entire dataset and indexes don't fit in memory anymore.
Yes, @ odometer is user variable. On every row, it first calculates the difference between the value in the current row and the (old) value stored in the variable as the first column, then sets the variable to value in the current row so that it is ready for a comparison when it iterates onto the next row.
User variables are also great for if you have a table that you need to add a sequential value to, e.g.:
I'm not sure it is optimal (you'd need to stick an EXPLAIN on it and benchmark to test, or a MySQL expert). One of your other options (i.e. the ones that don't involve lots of subqueries) would be to join the table with itself on a.id=b.id+1, but it relies on your ids being always sequential: