mysqldatabaseindexing

MySQL composite index for OR condition

I have a huge table on which I want to run a query with an or condition

SELECT id, upload_key 
FROM product_data 
WHERE (status = 0 or updated = 0) and uploaded_on is NULL;

The table is more than 50 million records now and takes minutes to execute. I tried creating a composite index on keys status, updated and uploaded_on (in the same order as query) but it as turns out, the query doesn't use composite indexes with an OR on two different columns.

Is there a way to create indexes to optimize the query, or should I just move uploaded_on as the first where clause and create an single column index on the same?

Solution

  • Write the query using union all:

    SELECT id, upload_key 
FROM product_data 
WHERE status = 0 and uploaded_on is NULL
UNION ALL
SELECT id, upload_key 
FROM product_data 
WHERE status <> 0 and updated = 0 and uploaded_on is NULL;

    You want two indexes: (status, uploaded_on) and (updated, uploaded_on, status).