I am trying to understand from the performance point what would be the difference . If I am comparing the time field as mentioned below ways
SELECT something from bucket b where b.fieldTimeStamp >= ‘2020-02-10 08.00.00’ and b.fieldTimeStamp<='b.dbdTriggerTimestamp >= ‘2020-02-10 09.00.00’
where the above time mentioned is always passed as current time and current time + 60 minutes
SELECT something from bucket b where b.fieldTimeStamp between date_format_str(CLOCK_TZ(‘GMT’, ‘1111-11-11 11.11.11’),‘0’,minute)’ and date_format_str(CLOCK_TZ(‘GMT’, ‘1111-11-11 11.11.11’),‘60’,minute)’
adding to it … does it scan the entire bucket and impact performance if I add the below line to the queries
b.fieldTimeStamp IS NOT MISSING
Any help is very much appreciated
Note : bucket consists of 500K to 2 million records at any given point of time
Why do you need to add b.fieldTimeStamp IS NOT MISSING?
Once you have predicate b.fieldTimeStamp BETWEEN it implies the b.fieldTimeStamp IS NOT NULL.
The performance wise you may not see much difference. But each document the predicate is evaluated so it i needs few cpu cycles.
If you do addition of 60 minutes on value like below it will NOT scan entire index.
CREATE INDEX ix20 ON default(fieldTimeStamp);
EXPLAIN SELECT something
FROM default b
WHERE b.fieldTimeStamp BETWEEN UTC_NOW() AND DATE_ADD_STR(UTC_NOW(),60,"minutes");
Check the spans of EXPLAIN statement it gives details.
Thanks @vsr1 for your reply . The n1ql query taking more time when querying with ‘IS NOT MISSING’ and without that its very fast and my understanding from your reply is that the ‘between’ doesn’t makes much difference when comparing the timestamps .
Adding to the other suggestion but the ‘addition of 60 minutes’ is a configured value when querying and might change when there is a lot of data
If you give only IS NOT MISSING it needs to get all the data. If you give specific dates and you right index it only gets that range. You query can configure if you want 60, 120,300 upto you. As range increases data increases and can take longer.