another thing that I have all over the place is a hierarchy: index on grandfather_table(grandfather) index on father_table(grandfather, father) index on son_table(grandfather, father, son)
almost all of my indices are composite. Are you thinking about composite indices with low cardinality leading columns? /Aaron ----- Original Message ----- From: "Josh Berkus" <[EMAIL PROTECTED]> To: "Palle Girgensohn" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Thursday, April 01, 2004 7:35 PM Subject: Re: [PERFORM] single index on more than two coulumns a bad thing? > Palle, > > > Is it always bad to create index xx on yy (field1, field2, field3); > > No, it seldom bad, in fact. I have some indexes that run up to seven > columns, becuase they are required for unique keys. > > Indexes of 3-4 columns are often *required* for many-to-many join tables. > > I'm afraid that you've been given some misleading advice. > > > I guess the problem is that the index might often grow bigger than the > > table, or at least big enough not to speed up the queries? > > Well, yes ... a 4-column index on a 5-column table could be bigger than the > table if allowed to bloat and not re-indexed. But that's just a reason for > better maintainence. > > -- > -Josh Berkus > Aglio Database Solutions > San Francisco > > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings > ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
