I have a table with the following structure:
Configuration ( id: binary(16) Primary key, htId: binary(16), raId: binary(16), date, amount );
I have already created these indexes:
idx_ht (htId) BTREE, idx_ra (raId) BTREE, idx_date (date) BTREE, idx_ht_ra_date (raId, htId, date) BTREE.
The table has nearly 150M records.
Here is the slow query with multiple htId-raId pairs that I want to optimize.
Select htId, raId, SUM (amount) as totalAmount From Configuration r Where ((r.htId = htId_1 AND r.raId = raId_2) OR (r.htId = htId_3 AND r.raId = raId_4) ... ~1000 pairs more (r.htId = htId_X AND r.raId = raId_X)) AND date Between fromDate AND toDate Group by r.htId, r.raId Having Count(1) = DATE_DIFF(fromDate, toDate) Order by totalAmount ASC LIMIT 0,100
Is there any way to optimize the query above? Seem that the indexes do not have any affect on my query when the query has multiple ORs like that. It take a lot of time to query.. Any help will be much appreciated. BR.