Should I use index on a table that is updated frequently?

Question

There are are 150 000 records in my comments table and about 1000 new comments are added per day... I wonder if I should use indexes on a table like this? Will it have positive or negative impact on performance? I use mysql...

Answer 1

Compare the number of INSERTs and SELECTs in this table. You do 1000 INSERTs per day, which is even not one per minute. But you do probably many more SELECTs when showing the results on the webpage. And if the visitor has to wait a few seconds each time he clicks on a link to view more comments, then you do the math...

EDIT: Use index.

Answer 2

It has for sure negative impact and might also have positive impact:

Negative: every index must be updated whenever you insert/delete a row or update an index column.

Positive: data retrieval might have a HUGE benefit from an index (if defined properly)

However, 1000 inserts per day isn't really much, you have very good chances that the benefits of properly defined indexes will outweigh the overhead.

Answer 3

You shouldn't index frequently updated columns like view_counter etc.

Answer 4

Without seeing what kind of SELECT queries you are doing and what the schema is, it is impossible to tell whether indxes will even be used, let alone help. E.g., if your data has low cardinality, or you are doing queries like

where MyColumn like '%somestring%'

then indexes will not help. Need more info.