Confusion over query stratergy

[Ian Christian]

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]