Mysql – What would be a optimal index for this query in thesql

indexindex-tuningMySQLperformancequery-performance

I'm trying to optimize this query. 

SELECT * FROM sales_reps 
WHERE account_id = 1 
AND (state = 'enabled') 
AND (clients >= 5 OR revenue >= 10000) 
ORDER BY name, platform, client_group, from_date

All the field in the ORDER BY clause are string except from_date which is a date. (account_id, name, platform, client_group, from_date) is a unique key combination.

I tried creating indexes with combination or those fields but when I do explain it doesn't seem to be using it. The table would have about 2 million rows for each account.

Any thoughts on how I could go about optimizing it?

Update 1: Here is the explain and result:

EXPLAIN SELECT * 
FROM `sales_reps` 
WHERE `sales_reps`.`account_id` = 1 
AND state = 'enabled' 
AND (clients >= 1 OR revenue >= 100) 
ORDER BY name,platform,client_group,from_date

Here is the result

id select_type table      type possible_keys           key  key_len ref  rows   Extra
1  SIMPLE      sales_reps ALL  account_id_4,account_id NULL NULL    NULL 33215  Using where; Using filesort

account_id_4 is the index with the following keys account_id, state(10), name, platform, ad_group, from_date, clicks, impressions

Update 2: Here is the full table structure:

CREATE TABLE `sales_reps` (
  `account_id` int(11) NOT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `platform` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `client_group` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  `state` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `status` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `max_cpc` decimal(8,2) NOT NULL DEFAULT '0.00',
  `match_type` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `first_page_cpc` decimal(8,2) DEFAULT '0.00',
  `top_page_cpc` decimal(8,2) DEFAULT '0.00',
  `quality_score` decimal(8,2) DEFAULT '0.00',
  `revenue` int(11) DEFAULT NULL,
  `clients` int(11) DEFAULT NULL,
  `conversions` int(11) DEFAULT NULL,
  `cost` decimal(8,2) DEFAULT '0.00',
  `average_position` decimal(8,2) DEFAULT '0.00',
  `campaign` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `phone_view` int(11) DEFAULT '0',
  `website_view` int(11) DEFAULT '0',
  `request_submit` int(11) DEFAULT '0',
  UNIQUE KEY `account_id_4` (`account_id`,`state`(10),`name`,`platform`,`client_group`,`from_date`,`clients`,`revenue`),
  KEY `account_id` (`account_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Best Answer

I have a few suggestions that may help

SUGGESTION #1 : Use index with different column order

ALTER TABLE sales_reps ADD UNIQUE INDEX
(state,account_id,name,platform,client_group,from_date);

Note that state and account_id are reversed. That way, there is no need to traverse all rows for a given account_id that would separate enabled from disabled. Only enabled entries for account_id are read.

SUGGESTION #2 : Refactor the Query

Instead of

SELECT * 
FROM `sales_reps` 
WHERE `sales_reps`.`account_id` = 1 
AND state = 'enabled' 
AND (clients >= 1 OR revenue >= 100) 
ORDER BY name,platform,client_group,from_date

Try this experiment

First run this query

SELECT enabled,accountid,name,platform,client_group,from_date
FROM sales_rep WHERE account_id=1 AND state='enabled'
ORDER BY enabled,accountid,name,platform,client_group,from_date

then run this query

SELECT * FROM sales_rep WHERE account_id=1 AND state='enabled'
AND clients >= 1 OR revenue >= 100

If the performance of those two queries are fast, then JOIN them as subqueries and see

SELECT B.* FROM
(
    SELECT enabled,accountid,name,platform,client_group,from_date
    FROM sales_rep WHERE account_id=1 AND state='enabled'
    ORDER BY enabled,accountid,name,platform,client_group,from_date
) A LEFT JOIN
(
    SELECT * FROM sales_rep WHERE account_id=1 AND state='enabled'
    AND clients >= 1 OR revenue >= 100
) B USING (enabled,accountid,name,platform,client_group,from_date)
WHERE B.enabled IS NOT NULL;

Give it a Try !!!

Related Question