1

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?

  • What is your database? – Gerard H. Pille Jun 13 '18 at 7:37
  • I use h2 1.4.196 – Herr Derb Jun 13 '18 at 7:50
  • You are using a Java database on an embedded system? Or were you already using Java for your application too? – Gerard H. Pille Jun 13 '18 at 8:32
  • In technical terms it's actually not embedded. We have a onboard processor with memory, that can run linux system on which we run our java application. We only feel the hardware performance limitation. – Herr Derb Jun 13 '18 at 8:38
  • Why is ERRORNUMBER not part of CLEARED_SOURCE_SORTED_FAILURES? – Lennart Jun 13 '18 at 8:51
1

I hope for you that most failures are cleared. In that case, when you query for cleared failures, the index will hurt performance. If you'd put that column in the second place, on the other hand, it might be benificial. When you're looking for not cleared - if those are rare - the index will help.

  • Most failures will be cleared indeed. I probably will have up to 20 open failures at once. This is coincidence though. Lets assume it would be the opposite. Why would it hurt? I thought that it simply wouldn't bring any benefit. – Herr Derb Jun 13 '18 at 7:19
  • 1
    Using an index while it would be better to do a full table scan, can bring performance to its knees. But for now, keep in mind that 15.000 records is nothing. – Gerard H. Pille Jun 13 '18 at 7:23
  • I always thought that in worst case a bad index is as performant as a full table scan. Looks like this is a may be a misunderstanding with impact. Why is a full index scan worse that a full table scan? – Herr Derb Jun 13 '18 at 7:26
  • We work on a embedded system, therefore we don't have the usual server performance :) We already feel bad indexs with 15000 rows, that's why I currently try to optimize it. – Herr Derb Jun 13 '18 at 7:30
  • 1
    The index may not contain all data you need - I see you selecting "*" (don't!). In that case, the database has to skip from index to table continuously. I assure you, it is a magnitude slower. – Gerard H. Pille Jun 13 '18 at 7:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.