Optimising a trivial MySQL query to get rid of filesort

Question

Hi, I've got a trivial mysql query which isn't liked by the server really:

SELECT zone_id, SUM(inc_sec) AS voucher_used
  FROM cdr_bill_2010_09
 WHERE cust_id = 1234 AND voucher_id = 'XXXXXX'
 GROUP BY zone_id

Now this should be really quick (and usually is) since there are indexes on both cust_id and voucher_id (voucher_id is chosen). However it still uses helper tables. After explaining:

           id: 1
  select_type: SIMPLE
        table: cdr_bill_2010_09
         type: ref
possible_keys: cust_id,voucher_id
          key: voucher_id
      key_len: 9
          ref: const
         rows: 1
        Extra: Using where; Using temporary; Using filesort

Can I do something specific to get rid of those? I'm running debian's 5.0.45.

Answer 1

Are you indexing all the bytes in the voucher_id?

from http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html :

You index only a prefix of a column named in the ORDER BY clause. In this case, the index cannot be used to fully resolve the sort order. For example, if you have a CHAR(20) column, but index only the first 10 bytes, the index cannot distinguish values past the 10th byte and a filesort will be needed.

I know it's not an order by clause, but maybe the same deal?

Answer 2

Hi, just try to add another index (according to http://forums.mysql.com/read.php?115,57443,59562):

CREATE INDEX index_zi ON cdr_bill_2010_09 (zone_id,inc_sec); 

Answer 3

Have you tried creating an index with both cust_id and vendor_id in it?

create index idx_cdr_bill_2010_09_joint on cdr_bill_2010_09(cust_id, vendor_id)

Answer 4

The actual answer was close to the other ones. Adding an index with both "where" key (voucher_id) or (cust_id) and the "group" key (zone_id) fixed the problem. Actually zone_id itself was sufficient to get rid of additional temporary / filesort, but adding another one was needed to use the speed up the basic query.