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?
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.
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)
DEFAULTing updated and never specifying it in the UPDATE? (No Triggers needed.)
– Rick James
Nov 10 '17 at 16:01
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
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.
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
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
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
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
WHERE updated_at > X? Surely a row cannot be updated before it is created, can it? – ypercubeᵀᴹ Nov 9 '17 at 0:06