Indexes in database

[martinalig]

Check if it might make sense to create additinal indexes in the database.

[ubamrein]

Is this still relevant? Any progress?

[iCesofT]

Hi... you should create an index like:

CREATE INDEX IN_DPPPT_GAEN_EXPOSED
    ON DPPPT.T_GAEN_EXPOSED (ROLLING_START_NUMBER, RECEIVED_AT);

When the number of requests is high and you have thousands of records....

[ineiti]

I remember there has been an issue with creating indexes on the keys, as this will reveal some information. I'll ask Carmela and Wouter...

[iCesofT]

IMHO you are not revealing any information. With indexes, you are optimising the database access when you are "plenty" of TEKs.

If you don't create the index, you are doing "full scan" every time you execute this query:

dp3t-sdk-backend/dpppt-backend-sdk/dpppt-backend-sdk-data/src/main/java/org/dpppt/backend/sdk/data/gaen/JDBCGAENDataServiceImpl.java

Line 101 in 288d099

String sql =

[ineiti]

So the problem is this: currently all new keys are batched together in buckets by setting RECEIVED_AT to the beginning of a bucket-period. What I'm not sure is what happens if you index the keys in the database: is it theoretically possible to retrieve the arrival order of the keys?

[ineiti]

OK - I looked at it the wrong way: the server knows anyway who did what, so this doesn't matter.

However, discussing with Carmela and Wouter, we concluded that the sorting should not be done on exposed_id, but on key, else the clients can get a much better guess if a connection has been a key submission or not.

So making an index is a good idea - how to add the index afterwards?

[iCesofT]

Hi @ineiti .. If you are saying to create an index on key field, you already have it as long as you have the unique index.

The key thing is that you should create indexes for optimising your queries. If you have a query like this:

dp3t-sdk-backend/dpppt-backend-sdk/dpppt-backend-sdk-data/src/main/java/org/dpppt/backend/sdk/data/gaen/JDBCGAENDataServiceImpl.java

Line 102 in 288d099

"select pk_exposed_id, key, rolling_start_number, rolling_period, transmission_risk_level"

You need to include those fields rolling_start_number and received_at in the index. Then you can sort by key so:

1. Create the index I said before. (Indexes in database #98 (comment))
2. Update your query to add the order by key field.

[ineiti]

Thanks for the explanation. The order by key is now replaced by a shuffle, which is already implemented for v2, but not yet for v1 - this is coming.

My original question is most probably known by Martin or Patrick: where to put those lines so the indexes are created correctly ;)