On 09/02/2011 12:02 PM, Mark Phippard wrote:
> On Fri, Sep 2, 2011 at 11:42 AM, Philip Martin
> <philip.martin_at_wandisco.com> wrote:
>> Yes, that's why I think we can delete them. However we rarely write to
>> these tables so the overhead of having the superfluous index is
>> negligible. There is no real need to remove them from 1.7 if people
>> would prefer to make a more minimal change for 1.7.
> I do not know about SQLite, but in other databases I have worked with,
> the database engine is intelligent enough to not create anything
> superfluous in this sort of scenario where the index you want to
> create already exists.
My experience is the opposite. Small database systems auto create
indexes for you. Big database systems treat the DBA as God when it comes
to what indexes or should not exist - even indexes that would seem very
desirable to create.
The index can frequently be as big, or sometimes bigger - than the table
itself. For updates to have to update both indexes multiples the number
of random writes that need to be done.
Minimize indexes wherever possible. Do not create them if they do not
need to exist. Do not include columns in the index unless they can be
demonstrated to provide value in expected user cases.
The sample index that somebody suggested seemed to include both the
GROUP BY column and the ORDER BY column in the index. I would test
without these before assuming they provide value in the general case...
--
Mark Mielke<mark_at_mielke.cc>
Received on 2011-09-02 22:31:52 CEST