Index not being used
Hi,
I have a summary table whose rows represent new users (as defined by the requirements) and each time a user is billed, their row is updated to keep a sum of the amount they've been billed in their first day, first week, first two weeks, and a few other time periods.
When viewing this data, the individual amounts are not important, but the sum and average of the revenue from people who are new users each week is. (Can you tell this is for marketing folks yet? :P)
My table is thus:
CREATE TABLE `newuser_summary` (
`customer` varchar(16) NOT NULL,
`called_number` varchar(16) NOT NULL,
`start_date` datetime NOT NULL,
`week_no` char(7) NOT NULL,
`client_id` varchar(16) NOT NULL,
`last_call_date` datetime NOT NULL,
`revenue_1day` decimal(18,10) NOT NULL,
`revenue_1week` decimal(18,10) NOT NULL,
`revenue_2week` decimal(18,10) NOT NULL,
`revenue_4week` decimal(18,10) NOT NULL,
`revenue_8week` decimal(18,10) NOT NULL,
`revenue_16week` decimal(18,10) NOT NULL,
`total_revenue` decimal(18,10) NOT NULL,
PRIMARY KEY (`customer`,`called_number`,`start_date`),
KEY `last_call_date` (`customer`,`called_number`,`last_call_date`),
KEY `start_date` (`start_date`),
KEY `arpu_calc1` (`called_number`,`week_no`),
KEY `arpu_calc2` (`client_id`,`week_no`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
(You might be wondering why we use start_date in the primary key. This is because marketing want to consider any customer who has not interacted with the system for more than 16 weeks as a completely new customer. It is for this reason that we store the last_call_date as well.)
The marketing folks want to be able to view, on a week-by-week basis, the amount of revenue generated by customers who entered our system in each week. They want to be able to limit the display either to a particular called_number or to a particular client_id.
This is facilitated by the week_no column, which is simply the concatenation of the year and the ISO 8601 week number of the start_date for that row. (E.g. if a new user entered the system today, their start_date would be something like "2009-04-14 11:41:00" and their week_no would be "200916").
So, if we wanted the total and average revenue generated by new customers with a client_id of '1' in their first week, the query looks like this:
SELECT week_no, COUNT( * ) AS new_users, SUM( `revenue_1week` ) /100 AS revenue, SUM( `revenue_1week` ) / ( COUNT( * ) *100 ) AS arpu
FROM `newuser_summary`
WHERE client_id = 1
AND week_no > '200712'
GROUP BY week_no DESC
(The "week_no > '200712'" condition is simply a way of limiting the amount of data shown.)
My problem is that despite creating an index (arpu_calc2) to speed up this query, MySQL is not using it according to the EXPLAIN statement.
EXPLAIN tells me that arpu_calc2 is a possible_key, but MySQL is using no key, type is 'ALL' and the Extra column tells me "Using where; Using temporary; Using filesort".
I already successfully added an index for querying while limiting to a particular called_number (arpu_calc1) and EXPLAIN says that MySQL uses it as expected, so why wont MySQL use the index for the query on client_id?
Thanks in advance,
Paddy O'Loughlin