How to improve the query performance using indexing?

Question

SELECT t1 . * FROM table1 t1, table1 t2
   WHERE t1.history_id < t2.history_id
     AND (t1.license_id = t2.license_id
        OR (t1.license_id IS NULL AND t2.license_id IS NULL))
     AND t1.op_id = t2.op_id
     AND (t1.service_date = t2.service_date
         OR (t1.service_date IS NULL AND t2.service_date IS NULL))
     AND t1.customer_id = t2.customer_id
     AND t1.serial_id = t2.serial_id.

The purpose of the query is to remove duplicated rows based on the above query conditions. The query join table 'table1' to itself. We have created index with group index for

1. license_id
2. service_date
3. customer_id
4. history_id (primary key)
5. op_id.

It executes correctly but with the addition of OR (t1.service_date IS NULL AND t2.service_date IS NULL) makes the query execution very slower. The table has more than 2 lacks of data.

We have used MySQL EXPLAIN command and here is the output

How can I improve the query execution time?

Answer 1

the problem is the 'IS NULL'. Plase read this for additional information: when to use IS NULL

One solution would be to remove the NULL values from your table (for example changing it to '0', since it's not a valid MYSQL autoincrement value), and then change the 'IS NULL' to '= 0'