I have table that contains failures. (15000 rows + on a abstract embedded system)
A failure has the following fields:
- id PK BIGINT
- errorNumber INT
- source VARCHAR
- raisedAt BIGINT
- clearedAt BIGINT
- cleared (computed column for sorting purposes = clearedat>0) BOOL
My first use case Is that I want to display all failures, the uncleared ones first.
Select * from FAILURES ORDER BY CLEARED asc, raisedAt desc;
Second use case is by a synchronization job. I want to delete all synchronized and cleared failures, there for all failures with an id smaller X and cleared
DELETE FROM FAILURES WHERE ID <? AND CLEARED;
Third use case is that I want to pick a specific uncleared failure.
SELECT * FROM FAILURES WHERE SOURCE=? AND ERRORNUMBER=? AND NOT CLEARED;
This would lead me to the following indexes:
CREATE INDEX CLEARED_RAISEDAT_SORTED_FAILURES ON FAILURE_MANAGER.FAILURES (cleared asc,raisedat desc); CREATE INDEX CLEARED_ID_FAILURES ON FAILURE_MANAGER.FAILURES (cleared asc,id desc); CREATE INDEX CLEARED_SOURCE_SORTED_FAILURES ON FAILURE_MANAGER.FAILURES (cleared asc,SOURCE desc,ERRORNUMBER desc);
I sometimes still struggle with index definition. It seems odd to me that I have 3 different indexes, which all only differ in the second column. Relative to my described uses cases, are these indexes feasible or does this create unnecessary overhead? Are there any check up questions that I can ask my self to figure if this might be the correct setting?