[EMAIL PROTECTED] wrote:
I'm still not convinced that over-indexing is harmful in this case. In general, too many indices is better than too few, and even if the data fits in memory, an indexed btree search will always be much faster than an unindexed search. Even if the table only has a few hundred small records, an indexed search still comes out vastly ahead, in my experience.
The point is, that an index normally holds a pointer to a data page in which the row can be found. So, the whole page must be read and searched anyways. Plus, the index page must be read. That means you have to read 32K of data, to find 130 bytes of data (when selecting a single row). It might vary depending on the exact implementation.
In contrast, storing the messages in a very ineffectively clustered way, like it currently seems to be, really costs read performance. It's not that there are not enough indexes, but you must also use the right ones. It seems like you prefer to define an infinite amount of indexes in the hope that you hit the right one sooner or later ;)
Another point is, that the MySQL optimizer might not be as mature as the one of Oracle (which should be pretty sophisticated) - it makes mistakes sometimes and especially choosing the right index can be a difficult task.
Michael _______________________________________________ DBmail mailing list [email protected] https://mailman.fastxs.nl/mailman/listinfo/dbmail
