6

I have a large table from which I must select large amounts of rows.

The table stores call detail records (CDR's). Example:

+-------------+--------------+------+-----+---------------------+----------------+
| Field       | Type         | Null | Key | Default             | Extra          |
+-------------+--------------+------+-----+---------------------+----------------+
| id          | int(45)      | NO   | PRI | NULL                | auto_increment |
| calldate    | datetime     | NO   | MUL | 0000-00-00 00:00:00 |                |
| accountcode | varchar(100) | NO   |     |                     |                |
| other...    | varchar(45)  | NO   |     |                     |                |

Since my queries look for a customers calls in certain dates, I indexed calldate and accountcode together in a clustered index like so:

CREATE TABLE `cdr` (
  `id` int(45) NOT NULL AUTO_INCREMENT,
  `calldate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `accountcode` varchar(100) NOT NULL DEFAULT '',
   other fields...
PRIMARY KEY (`id`),
KEY `date_acc` (`calldate`,`accountcode`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=latin1

However, when executing the following query, the EXPLAIN result shows that only the datetime portion of the key is being used:

Query:

SELECT * 
FROM cdr
WHERE calldate > '2010-12-01'
  AND accountcode = 'xxxxxx';

EXPLAIN result:

+----+-------------+-------+-------+---------------+----------+---------+------+---------+----------+-------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+-------+---------------+----------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | cdr   | range | date_acc      | date_acc | 8       | NULL | 3312740 |   100.00 | Using where |
+----+-------------+-------+-------+---------------+----------+---------+------+---------+----------+-------------+

It seems only the first 8 bytes (the date portion of the key) is being used. However the WHERE clause explicitly references both parts of the key with an AND, so in theory the full key should be used.

Should I create separate indexes for calldate and accountcode and let the query optimizer merge them? Why is the full index not being used?

Thanks for the help!

Alex Recarey
  • 20,178
  • 4
  • 25
  • 22
  • It looks like it filtered 100% of all rows with that query. Isn't that what the value of the filtered column is? Can you provide an example that does not have that filtering? I agree with one of the answers below, you should have the filter first, then the sort. index accountcode, calldate. You should get a much better result. – TheJacobTaylor Jan 04 '11 at 23:14

2 Answers2

6

Short answer: You'd be able to use the index more effectively here if your key was (accountcode, calldate) instead of (calldate, accountcode).

The best way to understand the problem is by thinking of multi-column keys as being a concatenation of the different columns. As an example if column 1 had values 'A,B,C,D' and column 2 'W,X,Y,Z' you'd construct an index on 'A-W, B-X, C-Y, D-Z' etc. and put all of those into a B-tree.

To do a range query, you find the first successor of low end of the range, and iterate till you exceed the upper range. This means that you can only effectively use the index to do a range query on a suffix of the key.

Vinay Pai
  • 7,432
  • 1
  • 17
  • 28
  • Thanks for the insight on how range queries work on multi column keys, the explanation was very valuable! Indeed the full index is used if the keys are ordered the other way round. – Alex Recarey Jan 05 '11 at 00:06
  • @Vinay, Well that's provided **if** `accountcode` has greater cardinality than `calldate`. – Pacerier Feb 01 '15 at 23:42
1

Since you're looking for a range of dates (> '2010-12-01'), I don't see how the optimizer could use the full index. The best it can do is scan the range of dates looking for the matching accountcode. Now, if you were looking for exactly one date and exactly one accountcode, then I'd expect the full index to be used.

Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235