How to refactor a SQL query with multiple OR statements for better performance?

Question

Using MariaDB.

The query is the following:

select  * from table    
where  id > 0  and
( code_field = :code1 and date_field = date(:date1) )  or  
( code_field = :code2 and date_field = date(:date2) )  or  
( code_field = :code3 and date_field = date(:date3) )  or  
...................................................... -- a few thousands combinations  
( code_field = :codeX and date_field = date(:dateX) ) 

Refactoring using the IN clause is not an option, since it does result to a Cartesian product.

select  * from table    
where  id > 0  and
code_field in (:code1, :code2) and
date_field in (:date1, :date2)

Is there a way, either with native SQL or with HQL to improve this query?

Answer 1

MariaDB understands tuple equality, so you could write the conditions as:

where 
    id > 0 
    and (code_field, date_field) in (
        (:code1, date(:date1)),
        (:code2, date(:date2)),
        (:code3, date(:date3)),
        ...
        (:codeN, date(:dateN))
    )

This might take advantage of an index on (code_field, date_field, id).