Looking for ways to optimize the following query in MySQL. I have tried creating a multi column index on sales_date, serviceID, and initalStatus but it does not get used. I've tried to research but am new to optimization and cannot seem to find an answer that fits. Below is the query:
SELECT
COUNT(id) as TotalAccounts,
AVG(sale_value) AS SaleValue,
AVG(credit_card = 1) * 100 AS CreditCard,
SUM(CASE WHEN pre_status = 1 AND bill_status = 'current' THEN 1
ELSE 0
END) AS Active,
SUM(CASE WHEN pre_status = 1 AND bill_status = 'past' THEN 1
ELSE 0
END) AS PastDue,
SUM(CASE WHEN `status` = 0 AND bill_status = 'past' THEN 1
ELSE 0
END) AS Canceled
FROM table_x
WHERE sales_date >= CAST('2015-01-01' AS DATE)
AND sales_date <= CAST('2016-01-01' AS DATE)
AND serviceID = 1
AND initialStatus = 1
And the EXPLAIN output:
id: '1',
select_type: 'SIMPLE',
table: 'table_x',
type: 'ALL',
possible_keys: 'sales_date,Combo sales_date office_id,salesDate_serviceID_initalStatus',
key: NULL,
key_len: NULL,
ref: NULL,
rows: '177585',
Extra: 'Using where'
For context, total records: 204,830. Records in my date range: 65,491.