Which DB and what Indexes best suit for the case?

Question

I need to create a DB which will store trades data for different exchanges. The table columns are 'exchange name', 'timestamp' and 'data' (JSON string). The only query I would need is 'get data for one or many exchanges with timestamp in defined range.

I don't think that amount of data will ever by more than 1GB and I'll keep DB locally on my laptop.

What would be best choice (in terms of speed) DB option: MySQL, PostgreSQL or SQLite? What indexes should I create?

For 1gb talking about speed at first place is unnecessary. All choices will do the job. — NiVeR, Aug 04 '18 at 18:58

score 1 · Answer 1 · answered Aug 06 '18 at 14:01

1

With 1 GB, there will be no noticeable speed difference. But with a self-contained laptop application, you do not need a client/server systen, so you should probably use an embedded database with less administration overhead.

You should create an index on the lookup column that has the highest selectivity in your queries, i.e., on the timestamp column. If there are enough different exchanges, it might make sense to include the exchange column in that index.

answered Aug 06 '18 at 14:01

CL.

173,858
17
217
259

Thanks. But if there are few exchanges (up to 5). Doesn't it make sense to include this column in index anyway as all queries will have exchange in where clause? – Alex Velickiy Aug 06 '18 at 15:26
That depends. Measure it. – CL. Aug 06 '18 at 16:40

score 0 · Answer 2 · answered Aug 23 '18 at 04:44

0

Use any RDBMS.

Use IN, not OR.

Have

INDEX(timestamp),
INDEX(exchange, timestamp)

answered Aug 23 '18 at 04:44

Rick James

135,179
13
127
222

Which DB and what Indexes best suit for the case?

2 Answers2