do i really need an index in my table?

I have a table called brute to account the errors during login and stuff and prevent brute force attacks 开发者_运维知识库showing a captcha.

it has the fields: domain, ip, time

• Deletions are based in the time field. ex: delete all entries older than 1 day.

• Selections takes all three columns to detect a possible attack. ex: 5 entries (errors) of ip xxxx in login in less than 10 minutes.

The question is: do I really need an index (primary or not)? Some friends are telling me i need but it just feels unnecessary.

obs: I've stumbled in similar questions around here. Although helpful, they couldn't solve my question entirely for this case. thank you (:

Adding to what Oded mentioned, if the site is going to be internet facing (which it looks like it will be), I suggest you adding indexes as that table might become very, very big. Specially after a few of the nasty things on the internet crawl your site and start trying to guess a login.

In some cases the reorganization of an index (B-Tree) on bulk delete operations might be more expensive than the sequential scan. Íf performance becomes an issue you should try to add an index and measure the required time. Without an index all data has to be read and filtered (by the where-clause, sequential-scan) but without additional costs. for reorganizing the index.

Reducing the number of index on your table will indeed increase write performance.

The question is, will the queries you do be helped by an index - and this depends on several things:

• The size of your table
• The time requirement of said queries
• Whether or not the queries would be helped by an index

I would suggest taking a sample table and running tests. use EXPLAIN to help understand how your indexes are being used.

Yes in general you should index the columns you sort, query, update and delete by.