Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

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;
share|improve this question
Can you afford to make a (account_id, state, name, platform, client_group, from_date, clients, revenue) index? – ypercube Oct 8 '12 at 18:24
yes. and i tried that.. but in explain it shows it as a possible but doesnt use it.. – Addy Oct 8 '12 at 18:29
Try it and also change the SELECT * to SELECT name, platform, client_group, from_date, clients, revenue, selecting only the columns in the index. – ypercube Oct 8 '12 at 18:30
Oh, just read the "2M rows for each account". I had misread as "the table has 2M rows. Nasty. I suggest you edit the question and add the actual CREATE TABLE script and the execution plan that the query gives. – ypercube Oct 8 '12 at 18:33
i did.. no luck.. 2 million is an estimate.. we will probably have about 50k a month and the account will last about 2 years.. so atleast a million – Addy Oct 8 '12 at 18:34
show 11 more comments

1 Answer

up vote 1 down vote

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 !!!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.