On Fri, Oct 17, 2008 at 2: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 > Hi, In my experience derived table performance often sucks. This does not feel like a place where it should suck, though. My guess is that the limit is messing with EXPLAIN's head. I have experienced LIMIT causing EXPLAIN output to show full table scans while the query performance did not act that way, and the servers stats did not show it either. How long does the second query actually take to run? -- Rob Wultsch
