Re: Having too many indexes on a table

Sukumar Konduru wrote: > > Hi > > I have a table with 10 columns, and row size is 261 and index size > is 117. > I need indexes on these columns. > > 1. userid, startdate > 2. userid, wcc > 3. wcc, startdate > 4. sesssionid ( I do not do query on this, But it is primary key for > making row unique) > > I need to know about these problems > > 1. Is it slows down to have too many index. If it slows down for > insertion and delete it is > OK. But is it slows down "select " also? Having many indexes slows down any row modification operations: INSERT, DELETE, UPDATE. If the indexes are appropriate for the queries you run, then they will speed up the query process. You should, however, ensure that the indexes are used, probably by using SET EXPLAIN ON to monitor which indexes are actually used. > 2. If I decrease row size, i.e dropping some of coulmns, does it > improve "select" performance. It depends on whether you do 'SELECT *' or 'SELECT col1, col2, ...'. If you use the * notation, then smaller row sizes marginally improve performance -- the increase can be big if your reduced size row allows more rows per page. > 3. Is there any order for creating indexes. Not that I'm aware of... > 4. How to do update statistics for this combination Pass. > Right now I do have indexes on 2,3,4 (the listed one). I have data > about 400,000 rows. It is slow many times. Check what SET EXPLAIN says about your queries.