Search optimization for rare event and date other than _time

[yogip86]

I am want to improve the response time for search with rare event and searching with date variable.

Note: I am interested in latest 500 event only. Here "local date" is other than _time variable. For _time is set on other date variable in event which i we can't change.

Scenario 1 : 

index="ABC" earliest=-120d sourcetype="XYX" flag = "Y" xxnumber="8XV5F5FF4"
| head 500| fields id

This search has completed and has returned 6 results by scanning 18 events in 0.254 seconds

Scenario 2 : 

index="ABC" earliest=-120d sourcetype="XYX" flag = "Y" local_date="2020-06-01 00:00:00"
| head 500| fields d

This search has completed and has returned 22 results by scanning 469,911 events in 25.058 seconds 

 Scenario 3: Rare events

index="ABC" earliest=-120d sourcetype="XYX" flag = "Y" local_date>="2020-06-15 00:00:00"
| head 500| fields id

This search has completed and has returned 57 results by scanning 2,943,130 events in 67.789 seconds

if we use any other filter than date filter (which is other than _time) takes less time (less than seconds). 

Can any one suggest how i can get better response time with date filters.?

[richgalloway]

Comparing date strings for > or < won't work as expected.  Try converting them to integers.

index="ABC" earliest=-120d sourcetype="XYX" flag = "Y" local_date=*
| eval local_epoch = strptime(local_date, "%Y-%m-%d %H:%M:%S")
| where local_epoch >= strptime("2020-06-15 00:00:00", "%Y-%m-%d %H:%M:%S")
| head 500| fields id

[yogip86]

@richgalloway i just tried this .. its taking almost similar time..  

This search has completed and has returned 57 results by scanning 2,943,130 events in 72.464 seconds