Question 17: Which index would support the following query the best?

 
Observe the scenario below:

CREATE TABLE `sales` (
  `site_key` int(11) default NULL,
  `sales_date` date default NULL,
  `product_key` int(11) default NULL,
  `sales_amount` decimal(10,2) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- test data generation
create table gen(a1 bit(1));
insert into gen values (1),(1),(1),(0),(1),(1),(1),(1),(0),(1);
insert into sales select floor(rand()*10),
            adddate('2010-01-01',
	    interval rand()*360 day),
            rand()*5000,rand() * 1000
  from gen v1,gen v2,gen v3,gen v4;
--

 
Which index would support the following query the best?

select sales_date,sum(sales_amount)
  from sales where site_key=5 and
    sales_date between '2010-01-01' and '2010-01-31'
  group by sales_date;

a) create index ix1 on sales(site_key,sales_date);
b) create index ix1 on sales(sales_date,site_key);
c) create index ix1 on sales(sales_amount);
d) create index ix1 on sales(product_key);

 
[ Tables and Indexes (15%) - Indexes ]