On Mon, 2005-11-07 at 09:53 -0800, K C wrote: > 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.
The semantics here are confusing; In case of UNIQUE(a,b), UNIQUE(b), the UNIQUE(a,b) isn't superfluous because searches on "a" alone cannot take advantage of UNIQUE(b) but they CAN take advantage of UNIQUE(a,b). On some systems, searches on "b" can take advantage of UNIQUE(a,b) - but not all. Because UNIQUE(b) is implied by PRIMARY(b) [or written out: b PRIMARY KEY], I have chosen to simply refer to its index as PRIMARY, and not necessarily the physical structure of the database. Why does anyone want to drop any index? Do we know of a system that will benefit from having a single UNIQUE(a,b) - because AFAIK, no useful system will benefit in this situation from having a single UNIQUE(b) > 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 > [email protected] > http://twister.fastxs.net/mailman/listinfo/dbmail-dev > > _______________________________________________ > Dbmail-dev mailing list > [email protected] > http://twister.fastxs.net/mailman/listinfo/dbmail-dev -- Internet Connection High Quality Web Hosting http://www.internetconnection.net/
