If you're talking about mysql I won't argue as I know nothing about it. But for pgsql, the primary key index can't be dropped:
test=> drop INDEX tt_pkey ; ERROR: cannot drop index tt_pkey because constraint tt_pkey on table tt requires it HINT: You may drop constraint tt_pkey on table tt instead. Besides, if you drop primary key index, you lose the ability to search on id. Kevin On 11/7/05, Geo Carncross <[EMAIL PROTECTED]> wrote: > > On Sun, 2005-11-06 at 14:28 -0800, K C wrote: > > I've replaced this kind of unique indexes with just normal > > (physmessage_id) if there is no other index can index physmessage_id. > > This is to make sure physmessage_id can use index. But unique index > > on (physmessage_id, id) is unnecessary here. > > People search for physmessage_id- which isn't unique. Knowing that when > it's with an id, it's also unique, this means database engines don't > need a second index for PRIMARY. > > If your database engine blindly creates and maintains implicit indexes > that are going to contain the same data as another index, consider > dropping the PRIMARY one- that's the superfluous one. > > Unfortunately, when we do that, we lose the ability to search on id > quickly because many database engines treat UNIQUE(p,q) as UNIQUE(p||q) > which, as long as we have "p", we can still use this index without "q" - > hence the reason it's UNIQUE(physmessage_id,id) and not > UNIQUE(id,physmessage_id) > > If you don't believe it, load a dbmail installation with 20G of data. > Should be easy- steal from mailing lists. Then do a data-only dump of > your favorite database server, and start playing with the data > structures. Most RDBMS come with an execution-planner that can be used > to speed profiling, but a stopwatch and an email client will do fine. > > -- > Internet Connection High Quality Web Hosting > http://www.internetconnection.net/ > > _______________________________________________ > Dbmail-dev mailing list > Dbmail-dev@dbmail.org > http://twister.fastxs.net/mailman/listinfo/dbmail-dev >
