On Fri, Oct 17, 2008 at 9:56 AM, Rob Wultsch <[EMAIL PROTECTED]> wrote:
> 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? > *How long does the second query actually take to run compared to first? Also, you could do this as a join, and it would probably be somewhat cleaner.... -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim)
