How should I index the following financial data schema?

Suppose I have some stock market data with the following fields

STOCK, DATE, BID, ASK

My most common queries will be of the 开发者_运维知识库following type

where STOCK=... AND DATE=... AND BID=...

Along with

where STOCK=... AND DATE=... AND ASK=...

The two main options I'm considering are:

1. Indexing 

a) STOCK, DATE, BID
b) STOCK, DATE, ASK

2. Indexing 

a) STOCK, DATE
b) BID
c) ASK

I'm not sure how to choose between the two

You should use the indexes that satisfy your WHERE clauses, i.e. the first option you give.

Of the second set of indexes, it's unlikely that b) and c) would ever be used. a) would be used, but would be less performant than those indexes listed in 1.

It's worth bearing in mind that most DBMSs will rarely use multiple indexes in satisfying a select from a single table - so when it comes to index on stock / date, and another on bid, one or other will be used if appropriate - but not both.

If the dates are for a BID and ASK are the same date, I'd put them in one table - it's just simpler and easier when you have less tables.

It would also make queries like this possible:

select * from my_table where ask - bid > 5

Create these indexes to suit your where clauses:

create index idx1 on my_table(stock, date, bid);
create index idx2 on my_table(stock, date, ask);