0

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.

  • 1
    Check the query plan by using Explain. If index is not used, you can FORCE the index scan, using FORCE INDEX option. mysqltutorial.org/mysql-index/mysql-force-index , dev.mysql.com/doc/refman/5.7/en/using-explain.html – sudalai Jun 7 at 16:53
  • ... WHERE (r.htId, r.raId) IN ((htId_1, raId_2), (htId_3, raId_4), ..., (htId_x, raId_x)) and ... – Akina Jun 7 at 18:40
  • @User2397 If index is not used, you can FORCE the index scan, using FORCE INDEX option. ... but remember that in most cases you do not see something that is visible to the server - in other words, forcing index usage decreases performance in most cases. – Akina Jun 7 at 18:42
  • You can use use the output of SHOW CREATE TABLE foo\G to give us table and indexes at the same time. The output of EXPLAIN EXTENDED would also be useful! Something is wrong/missing here AND toDate. Also, DATE_DIFF(fromDate, toDate) isn't sargable - you might want to use a simple subtraction instead? You might want to convert your 1000 OR criteria into a table and join on that instead of having such a construct? Oh, yes, define slow? – Vérace Jun 7 at 18:44
  • What table is raId_2 in? Ditto for the rest of the columns without a table qualification. Please provide SHOW CREATE TABLE. Are those UUIDs? – Rick James Aug 30 at 0:38
0

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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