indexing fields

[smartalco]

if i understand this correctly, indexing a field will speed up search results, presumably by listing all the values of that field in one hidden file, and when search, that file will be parsed first, instead of the entire tableso, im posting partly to confirm my theory and partly to ask exactly what type of fields should/shouldn't be indexed, based on what i said it would be best to index the field or two that is most often used to select records, like in a table of blog posts, you would want to index the date, since when creating the page with posts the blogs would retreived and ordered by date (actually thats kinda a bad example but i cant think of a good one :/)

[mluciuk]

Indexing should match the nature of the query you want to improve the perfromance of. Indexing on individual columns into individual indexes does little nothing to increase performance on a query where you are selecting based on two or more columns.ExampleTable T1 has columns C1, C2, C3........The query you wish to improve the performance on looks likeSelect * from T1 where C1 = value1 and C3 = value3A combined index on C1 and C3 will help.SQL optimizer can select only one index to use for the query. He will pick the best one available. He will not combine indexes nor will he tell you how to structure your index. It is up to you to create appropriate indexes.Having said that, note that there is overhead involved in the creation and maintenance of indexes. If you can afford the space and your database/application can afford the time to maintain the index (indexes are updated automatically as you update the data they are based on), an index can turn a snail query into a cheetah.Good luck