Why are you creating a subquery/derived table? Just change your limit to 1,2 ORDER BY updates.AcctSessionTime DESC LIMIT 1,2
Like you did in the outer query. Brent On Fri, Oct 17, 2008 at 5:12 AM, Ian Christian <[EMAIL PROTECTED]> wrote: > Hi all, > > I'm trying to work out the difference in a field between the last 2 > updates in an updates table. I'm doing this as shown below: > > mysql> SELECT > -> (@in - AcctInputOctets) AS AcctInputOctets, > -> (@out - AcctOutputOctets) AS AcctOutputOctets, > -> (@in := AcctInputOctets), > -> (@out := AcctOutputOctets) > -> FROM updates > -> WHERE acctuniqueid = '4b9fe4a361344536' > -> ORDER BY updates.AcctSessionTime DESC LIMIT 2 > -> ; > +----------------------+----------------------+--------------------------+----------------------------+ > | AcctInputOctets | AcctOutputOctets | (@in := > AcctInputOctets) | (@out := AcctOutputOctets) | > +----------------------+----------------------+--------------------------+----------------------------+ > | 18446744073654284768 | 18446744073171813223 | > 55266848 | 537738393 | > | 9508 | 18620 | > 55257340 | 537719773 | > +----------------------+----------------------+--------------------------+----------------------------+ > 2 rows in set (0.02 sec) > > mysql> explain (query above) > *************************** 1. row *************************** > id: 1 > select_type: SIMPLE > table: updates > type: ref > possible_keys: AcctUniqueID > key: AcctUniqueID > key_len: 34 > ref: const > rows: 327 > Extra: Using where; Using filesort > 1 row in set (0.00 sec) > > > As can be seen, this query uses a key, and runs well. However, I only > require the 2nd row of that dataset. I couldn't figure out a better > way of doing it than this: > > mysql> SELECT AcctInputOctets, AcctOutputOctets FROM > -> (SELECT > -> (@in - AcctInputOctets) AS AcctInputOctets, > -> (@out - AcctOutputOctets) AS AcctOutputOctets, > -> (@in := AcctInputOctets), > -> (@out := AcctOutputOctets) > -> FROM updates > -> WHERE acctuniqueid = '4b9fe4a361344536' > -> ORDER BY updates.AcctSessionTime DESC LIMIT 2 > -> ) AS t1 LIMIT 1,2 > -> ; > +-----------------+------------------+ > | AcctInputOctets | AcctOutputOctets | > +-----------------+------------------+ > | 9508 | 18620 | > +-----------------+------------------+ > 1 row in set (0.02 sec) > > > This does exactly what I want, but to me feels wrong, I think I'm > missing a trick to doing this 'the right way'. Also, look at how the > query runs: > > > mysql> explain SELECT AcctInputOctets, AcctOutputOctets FROM > -> (SELECT > -> (@in - AcctInputOctets) AS AcctInputOctets, > -> (@out - AcctOutputOctets) AS AcctOutputOctets, > -> (@in := AcctInputOctets), > -> (@out := AcctOutputOctets) > -> FROM updates > -> WHERE acctuniqueid = '4b9fe4a361344536' > -> ORDER BY updates.AcctSessionTime DESC LIMIT 2 > -> ) AS t1 LIMIT 1,2 > -> \G > *************************** 1. row *************************** > id: 1 > select_type: PRIMARY > table: <derived2> > type: ALL > possible_keys: NULL > key: NULL > key_len: NULL > ref: NULL > rows: 2 > Extra: > *************************** 2. row *************************** > id: 2 > select_type: DERIVED > table: updates > type: ALL > possible_keys: AcctUniqueID > key: AcctUniqueID > key_len: 34 > ref: > rows: 28717165 > Extra: Using filesort > 2 rows in set (0.02 sec) > > > Apparently, it's doing a full table scan over all 29 million records. > Whilst this query appears to run fast still, surly it's not right that > a full table scan is needed? > > Thanks, > > Ian > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
