5 replies to this topic

[BASHERS33]

I am not too experienced with basics, but I am wondering if someone can give some rule of thumb as to when something should be indexed and when not.

Someone once said index everything that would ever be in a where clause. But in some tables that is making me have to index practically everything. Also what if I have only a few instances where 3 particular fields would be in a where clause , but in most cases only one of them would be? Would I still group them together in an index?

Thanks

[AlanQ]

Yes, it shouldn't matter if you have to index in a group, let me know if it works fine.

[BASHERS33]

Well I really indexed everything a few months ago, including some in groups. It's not easy to tell in a live environment whether it will make much difference though since I have so little data. I "think" I indexed properly, but also some tables have practically every field indexed and Ihave a feeling that isn't such a great idea. I know many people would rather use a ton of DB space than to have the program less effective due to not having a particular index, but I am guessing if I sell this most people will be on shared hosting where DB space can matter more often.

[Orjan]

normal indexing rule says index fields that is often ordered by or filtered upon. the bigger database, the more need for indexes.

[WingedPanther]

Most databases include a profiler that can be used to examine the kinds of queries that are actually used and even offer suggestions for new indexes (MySQL 5.0 has a profiler, but I'm not familiar with what all it does). The reality is that every index is a tradeoff. An index speeds up queries, but also increases the size of your database. Creating an index for a set of fields that is rarely queried against is likely to help bloat your database with no real benefit.

Let's say you have a table with five fields: field1, field2, field3, field4, and field5.
First of all, that allows you to have 2^5 -1 or 31 possible indexes. Nobody does that. However, if 50% of the queries you use have field1 and field2 in the where clause, that's a great candidate for an index. If another 25% of the queries have just field1 in the where clause, that is its own index (not covered by field1 and field2!). If less than 1% use just field3, it probably isn't a good candidate to be an index.

[BASHERS33]

ok, thanks. When I get a chance I am going to try to fine tune all fo that and lose some indexes probably. Also I never could remember how to figure out number of possible combinations (unrelated to programming) so that example was helpful for that also. lol