SQL - speed up query

I currently use the following query which takes about 8 minute to return the result due to the volume of data (About 14 months). is there a way I can speed this up please?

The database in question is MySQL with InnoDb engine

select
    CUSTOMER as CUST,
    SUM(IF(PAGE_TYPE = 'C',PAGE_TYPE_COUNT,0)) AS TOTAL_C,
    SUM(IF(PAGE_TYPE = 'D',PAGE_TYPE_COUNT,0)) AS TOTAL_D
from
        PAGE_HITS
where
    EVE_DATE >= '2016-01-01' and EVE_DATE <= '2016-01-05'
    and SITE =  'P'
    and SITE_SERV like 'serv1X%'
group by
    CUST

Data is partitioned by 6 months. Every column that goes into where clause is indexed. There a quite some indexes & would be a big list to list here. Hence, just summarizing in words. With respect to this query, EVE_DATE + PAGE_TYPE_COUNT is one of the composite indexes & so are CUST + SITE_SERV + EVE_DATE, EVE_DATE + SITE_SERV, EVE_DATE + SITE,

The primary key is actually a dummy auto increment number. It isn't used to be honest. I don't have access to the explain plan. I'll see what best I can do for this.

I'd appreciate any help to improve this one please.

Solution

Okay, as the table range partition is on EVE_DATE, the DBMS should easily see which partition to read. So it's all about what index to use then.

There is one column you check for equality (SITE = 'P'). This should come first in your index. You can then add EVE_DATE and SITE_SERV in whatever order I guess. Thus your index should be able to locate the table records in question as fast as possible.

If, however, you add the other fields used in your query to your index, the table wouldn't even have to be read, because all data would be avaliable in the index itself:

create index on page_hits(site, eve_date, site_serv, customer, page_type, page_type_count);

This should be the optimal index for your query if I am not mistaken.