|
On small or big tables, I would use indexes on fields used in WHERE, JOIN, and even ORDER BY clauses if reads significantly outnumber writes. If you aren't sure, you can measure both with a trace.
You don't maintain indexes - the server does. When you INSERT a new row in the table, MySQL makes a new node in the index. When you UPDATE, MySQL changes the table and the index before it returns control to your script. This is why changes are slowed down by indexes. This (and disk space) is why every column in every table doesn't get an index - just the ones that are most important.
MySQL will only use one index per table per query I think. Trying to use more than that is a very complicated operation called index intersection that only big league databases like Oracle are capable of. So that's another reason to go lightly, although one table gets used in many queries.
If a column has a unique index or constraint, and the database finds that value, it will stop reading the table data because it knows it won't find another copy. This can be expected to half execution time, but obviously only works when your data really is unique, and searchable as such.
Does MySQL support covering indexes? I'm pretty sure it allows compound indexes (ones with more than 1 column) but I don't know if, should a query only use columns that are all part of the same index, the query will be satisfied without actually touching the table physically? If that's the case, you could try and benchmark covering your small tables with a single index.
I realize this doesn't answer your question directly, but without careful and in depth study of your database and everything that uses it, a yes or no answer is impossible.
|
