Just to amplify what Michael has said.... If you had a table with 50 columns and created a separate index for each of those columns, then you'd probably get some performance benefit from having those indexes. But you'd also have to absorb the following overhead: 1. For each new row that you created, you'd have to add an entry to each of the 50 indexes on the table. 2. For each row that you deleted, you'd have to remove an entry from each of the 50 indexes on the table. 3. For each row that you changed, you'd have to change the index entries on all columns that changed. 4. For each index you build, you'd have to pay for building that index and you'd have to pay for whatever storage that index used.
That adds up to a lot of overhead, likely far more than the benefit you got by indexing the columns in the first place. Rather than putting indexes on every column database designers tend to put them in the following places: 1. A unique index on the primary key (which is required on every foreign key to enable Referential Integrity). 2. A unique or non-unique index (as appropriate) on each foreign key to help performance of Referential Integrity and joins, which are frequently on foreign keys. 3. A unique or non-unique index (as appropriate) on any other columns where query performance is critical. 4. A non-unique index on the column (or combination of columns) that you want as your clustering key. (The clustering key governs the physical sequence in which data is stored.) [NOTE: I mostly use DB2 and clustering is a big deal there but I'm not sure if it works the same way in MySQL.] Rhino ----- Original Message ----- From: "Michael J. Pawlowsky" <[EMAIL PROTECTED]> To: "Jeffrey G. Ubalde" <[EMAIL PROTECTED]> Cc: <mysql@lists.mysql.com> Sent: Monday, November 14, 2005 8:19 PM Subject: Re: Newbie Question > Jeffrey G. Ubalde wrote: > > > Good day list! > > > > I would just like to ask a somewhat not so intelligent question. What > > is the downside of indexing almost all of the fields in a table? Is it > > advisable? > > > > Indexes have to be built... so if you did that, for every query that > alters the data many indexes will have to be written. A lot of extra > overhead if they will never be used. > Look to see where indexes are needed by the types of queries you are > writing. Add a slow query log to my.cnf. > This will give you a very good idea of where you might need some indexes. > Then trace the queries to make sure the indexes you've created are being > used. > > > Cheers, > Mike > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > -- > No virus found in this incoming message. > Checked by AVG Free Edition. > Version: 7.1.362 / Virus Database: 267.13.0/167 - Release Date: 11/11/2005 > > -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.0/167 - Release Date: 11/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
