Mysql – Locating all records relevant for a specific date

datedatetimeindexMySQLperformancequery-performance

I have a MySQL InnoDB table with about 1GB of records and growing.
each record in this table has, among other fields two date fields :

  1. AddedDateRecord – The date the record was added (datetime).
  2. LastUpdatedRecord – The date the record was last updated (datetime).

The problem is that if i want to find all records relevant for a single date i have to use 3 "OR" statements. assuming "date" is the date I need :

  1. AddedDateRecord >= date:00:00:00 AND AddedDateRecord <= date:23:59:59

OR

  1. LastUpdatedRecord >= date:00:00:00 AND LastUpdatedRecord <= date:23:59:59

OR

  1. AddedDateRecord <= date:00:00:00 AND LastUpdatedRecord >= date:00:00:00

I'm sure you can see the performance hit for using these 3 ORs in a Query on a 1GB records table;

I'm looking for a solution that does not involve moving away from MySQL.

Any help would be greatly appreciated.

Thanks

Best Answer

No INDEX can handle that OR. Nor can any combination of indexes.

UNION can help with the first 2 range tests, but it won't help with the 3rd.

The 3rd range may include one-fourth of the table.

Is there any limit on the time between Adding and Updating? We might be able to use a partitioning or spatial trick with that information.

Barring that, I recommend a UNION DISTINCT of the 3 tests, together with these two indexes:

INDEX(AddedDateRecord, LastUpdateDateRecord)  -- for Q1 and sometimes Q3
INDEX(LastUpdateDateRecord, AddedDateRecord)  -- for Q2 and sometimes Q3

The UNION is needed because MySQL is not good with ORs.

The DISTINCT is important because you can get dups. To be faster, change to ALL and worry about the case where both columns are exactly date:00:00:00 -- where all 3 tests succeed.

Don't trust what you find for LIMIT, unless there is a significant pattern to the order how the rows were inserted. Also, we need to see the PRIMARY KEY.

Related Question