On Fri, Jun 10, 2011 at 1:22 PM, <tv@fuzzy.cz> wrote:
>> If I had set the primary key to (diag_id, create_time) would simple
>> queries on
>> diag_id still work well i.e.
>> select * from tdiag where diag_id = 1234;
>
> Yes. IIRC the performance penalty for using non-leading column of an index
> is negligible. But why don't you try that on your own - just run an
> explain and you'll get an immediate answer if that works.
The effective penalty, which you don't see on your explain, is the
size of the index.
Depends on the data stored there, but the index can grow up to double
size (usually less than that), and the bigger index is slower for all
operations.
But, in general, if you need both a single-column a multi-column
index, just go for a multipurpose multicolumn one.