Timeline for How to make my query use the available index
Current License: CC BY-SA 4.0
9 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Feb 17, 2022 at 12:07 | comment | added | J.D. | @Zapnologica Yes there is some truth to what Charlieface said. I updated my answer accordingly to account for that. While I feel my first suggestion will still be faster than what you originally had because it makes the index covering, my additional suggestion should address the point Charlieface makes & still cover you for both your use cases. You may find the most performance gain with that definition. Unfortunately the only way to really know is to test both ways. My recommendation would be for you to create a copy of the table in a Development environment so you can test without downtime. | |
| Feb 17, 2022 at 12:06 | history | edited | J.D. | CC BY-SA 4.0 |
added 370 characters in body
|
| Feb 17, 2022 at 5:21 | comment | added | Zapnologica | That is exactly what I was thinking last night, I ONLY query by "RANGE" cause we will never know the exact millisecond which the time was at. How does that range search affect the index? I actually think this is why we put the index in the order we did, because sensorId will always be explit, key will be explicit if it is present else it wont be there, and time will always be a range scan. | |
| Feb 16, 2022 at 21:20 | comment | added | Charlieface |
@Zapnologica Note that this new index order does not help you as much if your query filters on a range of DateTimeUTC and an exact SensorId, it only helps if DateTimeUTC is exact. In other words, selectivity is not as relevant if you are looking at range predicates, because once you are searching by range you cannot filter the next column.
|
|
| Feb 16, 2022 at 13:26 | vote | accept | Zapnologica | ||
| Feb 16, 2022 at 13:26 | |||||
| Feb 16, 2022 at 12:06 | history | edited | J.D. | CC BY-SA 4.0 |
added 11 characters in body
|
| Feb 16, 2022 at 11:25 | history | edited | J.D. | CC BY-SA 4.0 |
deleted 19 characters in body
|
| Feb 16, 2022 at 11:13 | history | edited | J.D. | CC BY-SA 4.0 |
added 253 characters in body
|
| Feb 16, 2022 at 11:07 | history | answered | J.D. | CC BY-SA 4.0 |