MySQL – How to optimize multiple OR conditions in a query on a large table

MySQLmysql-5.6mysql-5.7optimization

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.

Best Answer

OR is hard to optimize, especially 1000 of them.

See if you can through the values in a table, then JOIN to that table.

Then provide EXPLAIN SELECT ....

Related Question