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/
