0

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

12
  • I'm sure you can see the performance hit for using these 3 ORs in a Query on a 1GB records table No. If proper indices exists then the query may be fast enough, espesially when output records amount is low. Also you may try to divide the query to 3 queries combined with UNION ALL. – Akina Nov 13 '19 at 7:01
  • Thanks for the comment Akina. The Query is running for about 40-50 minutes with at least one of the ORs (#3) needing a full scan of the table. This will obviously get worse with time. I don't think that breaking the query will help but i will try to be sure. I was thinking more in the direction of an extra precalculated field but not sure what would be the best option as each record may be relevant for up to 31 different dates (Record "life" is at most 1 month). – Boaz Yahav Nov 13 '19 at 10:02
  • Please show table's DDL (in CREATE TABLE form for to see all indices). Each separate query MUST be fast, and MUST use proper index if such index exists. Maybe you have no proper indices? – Akina Nov 13 '19 at 10:31
  • The relevant index is : KEY AddedDate (AddedDateRecord,LastUpdateDateRecord) After i started to check it seems that the problem may not be the actual time the query is running but the amount of records/data it returns. If i limit to 10 the query returns after 0.031 seconds with the data. If i limit to 5000 the query returns after 0.9 seconds but shows "fetching" for 69 seconds. If i limit to 10000 the query returns after 0.7 seconds but shows "fetching" for 258 seconds. This is done via client (work bench) but the real query runs local on the server. – Boaz Yahav Nov 13 '19 at 12:10
  • 1
    Key by (AddedDateRecord) is relevant to #1, by (LastUpdateDateRecord) is relevant to #2, and any of them is relevant for #3. Your key is relevant for #1 too, of course, but it is less effective because the its keysize is higher. – Akina Nov 13 '19 at 12:30
0

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.

2
  • After some consulting with a DBA, i got the same response regarding the ORs and the fact that Indexes will not help. That said, we decided to go with creating a temporary table as a subset of the huge table as the original query already had other JOINS which caused the main slowness.The idea is to create a much smaller subset and then use it to make the final JOIN. Partitioning is also considered. I also added a new index which dramatically shortened the time it takes to create the subset table so i guess indexes do help, even with the ORs...Still in testing so no final answer yet. – Boaz Yahav Nov 14 '19 at 11:37
  • @BoazYahav - When using UNION, Q1 and Q2 will be helped significantly with the two indexes I provided. Without UNION, I would be surprised if they helped much. Can you provide EXPLAIN SELECT ... – Rick James Nov 14 '19 at 22:16

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.