-1

I have to run a query where I need to find all records that were created OR updated within a date range.

The problem with this is that the data set is large and so a query in the past couple years can return a few million rows.

I think there are a couple problems here.

MySQL isn't going to be able to choose a key/index since it's doing created_at > X OR updated_at > X, so it's going to be slow. How can I speed this up?

  • Additionally, if you can provide us with the query execution plan so that we can pin point exactly the culprit, but I believe that a secondary index on the table is a very nice start. – WickedFan Nov 8 '17 at 22:06
  • Which version of MySQL? – ypercubeᵀᴹ Nov 8 '17 at 23:32
  • Are these two columns nullable? – ypercubeᵀᴹ Nov 8 '17 at 23:54
  • And why not use just WHERE updated_at > X? Surely a row cannot be updated before it is created, can it? – ypercubeᵀᴹ Nov 9 '17 at 0:06
4

OR is a difficult to optimize. 'Often' the fix is to rewrite using UNION.

( SELECT ... WHERE created_at > X )
UNION DISTINCT
( SELECT ... WHERE updated_at > X )

and have 2 separate indexes:

INDEX(created_at)
INDEX(updated_at)

Each SELECT will pick its optimal index. The UNION, alas, will involve a tmp table and a dedupping pass. Therefore, there are cases where a full table scan using OR is faster.

1

I usually implement updated to be set to the creation date on creation. It makes queries like this trivial as you only need to query on the update date.

Using triggers for both ensures they are correctly maintained.

On insert: created, updated = NOW

On update: created = old.created (prevents modification) update = NOW (ensures correct value)

  • Can't this also be achieved DEFAULTing updated and never specifying it in the UPDATE? (No Triggers needed.) – Rick James Nov 10 '17 at 16:01
  • @RickJames defaults are set on create so that works for create, The defaulted valued won't be changed on update. However, without triggers, either field can be overwritten. These are often part of what is know as audit data, so you should ensure they are correct. – BillThor Nov 11 '17 at 2:41
1

i think updated_at Date is nullable and created_at is not nullable.

If above is true then you can optimize the query in this manner. you can avoid or have to avoid Distinct and UNION

SELECT col1,col2,1 as Flag
FROM test
WHERE (createdate >=@FROM
    AND createdate <= @TO)
    AND (modifieddate IS NULL)

UNION ALL

SELECT col1,col2,0 as Flag
FROM test
WHERE modifieddate >=@FROM
    AND modifieddate <= @TO
  • Why did you add that flag? It doesn't seem to be needed. – ypercubeᵀᴹ Nov 10 '17 at 9:39
  • yup you are right.Suppose I will need to find exclusive record.Then it may help me recognise.Also there is no performance impact. – KumarHarsh Nov 10 '17 at 10:00
-5

The quickest solution here is to create non clustered index (secondary index) on your date field(s) with the unique key.

Avoid using the between statement as it is proven to be less efficient than using where date > x and date < y

Still in doubt? Run your query using TOP 100, you'll get a result set cycle finished and be able to determine if it suits your needs.

  • 3
    Where's the proof that BETWEEN is slower than >= and <=? The Optimizer turns one into the other. This is a MySQL question; TOP does not exist. (LIMIT does.) – Rick James Nov 8 '17 at 22:31
  • 1
    BETWEEN is "inclusive". "Inclusive" is usually wrong for DATEs and DATETIMEs. Or it is messy to construct due to date computation, leap-years, etc. So, I dislike BETWEEN for such. Instead I prefer >= and <. – Rick James Nov 8 '17 at 23:03
  • 1
    Feel free to make this all go away by deleting your Answer. – Rick James Nov 8 '17 at 23:10
  • 3
    @WickedFan I tried adding TOP 100 to a MySQL query but all I got was a syntax error and a lousy t-shirt – Tom V - Team Monica Nov 10 '17 at 9:35
  • 2
    Perhaps this point can be derived from the many comments above, but in none of those comments it seems to me explicit enough and I fear beginners might be confused. So for their sake I would like to say it once and for all (in this comment thread at least): x BETWEEN a AND b is equivalent to x >= a AND x <= b and not to x > a AND x < b or x >= a AND x < b (or x > a AND x <= b, for that matter). Proof. (This is just to clarify the equivalence point, not to argue about which is more appropriate and when.) – Andriy M Nov 10 '17 at 11:45

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.