[Dave#]

Originally Posted by squidsk

I think you have a misunderstanding of how things work. Indexes improve the performance of the database by speeding up searches of tables when the conditions of searches use columns that have been indexed. For example a query of the post table for all posts by a particular user (i.e. userid column) is much faster if the userid column is indexed than if it isn't. The cost of indexing a column (or groups of columns) is that it takes up space. The one thing that is not a cost is performance.

I think you are the one who is mistaken.

MYSQL decides how to execute a query based on a few things, indexes is one and data size is another.

It is very possible to add an index and degrade performnce because the MYSQL query planner decides to use the wrong, more inefficent Index.

From memory, the index was on last_modified on the Post table. We have 13M posts and the MYSQL query planner did not like this at all.

Also bear in mind that every new index on the POST table means slower inserts as MYSQL needs to update extra indexes.