On Mon, 2005-12-12 at 09:08 -0800, Kevin Brown wrote: > Aaron Stone wrote: [snip] > > Should we also add / change the indexes to uppercase? > > Hmm....probably, though only PostgreSQL allows you to create > functional indexes, so it's the only engine that'll benefit from that. > > The index creation would (using lower instead of upper as suggested > below) look like this: > > CREATE INDEX dbmail_headername_lower ON > dbmail_headername(lower(headername));
Which is possibly why MySQL doesn't want to use case sensitive matching in the first place; it would kill queries such as this one without a proper index. When I read your earlier email, I was definitely thinking, "perfect idea, but I'll switch that from upper to lower..." ;-) > Note that even with many tens of thousands of messages, the headername > table winds up with only a few hundred entries... [snip] > So the table is almost always going to easily be small enough that the > PG planner will choose a sequential scan over the table... Off the cuff, I would not expect any dip in performance with the index, regardless of what the PG planner chooses (indeed, in the case of 100 entries, sequential lookup will often be faster than anything else -- and it's basically a fixed cost anyways). For the odd case of someone whose messages do not have consistent headers, the index will save our butts from horrific performance. Aaron
