Which field should be the candidate for index in this case? You're tracking visits already in another table that contains quite some information about the visit.

In order to block duplicate visits after a certain amount (Let's say 5) you think its best to use a separate table that only contains the IP address and the date of the visit.

Which field should be indexed? IP address or Date? Both will be used in the query when you check for duplicate visits by that IP on a certain date.

Using both the fields will be an overkill? Is there any tool that can help with this estimation?

Table

CREATE TABLE `ip_visit`(
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`ip` int(10) unsigned NOT NULL, 
`date` date NOT NULL, 
 UNIQUE KEY `id` (`id`) 
)ENGINE=InnoDB DEFAULT CHARSET=latin1


This table is expected to receive 100k entries per day.
In order to make this blisteringly fast, what I would do is the following - with the proviso that I know nothing about your server RAM, CPU or disk config.
Bear in mind that a decade's records will be ~35M/year - so ~350M for a decade - most servers should be able to cope with a relatively small table like this - but again, I don't know what else is happening on your server.
1. Do not call a field &quot;DATE&quot; - it's an SQL keyword and this can

make debugging difficult, and,
make porting your app to a different server difficult should you choose to do so.

2. I would make id (perhaps visit_id would be better?) the PRIMARY KEY of the table - you already have practically that situation anyway.
Calling all tables' PRIMARY KEY &quot;id&quot; can again make debugging tricky, since you won't know which table's id field is causing any issues.
3. I would put an INDEX on both ip and visit_date - make any queries very fast - 100K/day ~ 1.2 inserts a second which any reasonable server should be able to easily cope with.
4. Your data is essentially historical.  Rename the table above to current_vist. Then you can create an EVENT (and/or Google &quot;How to create MySQL events&quot;) to fire at, say, 03:00 on Sunday morning (or whenever your system is quiet - in conjunction with your backup schedule).
Just a word about backups - always backup all of your data - it obviously does't need to be to hand for every record, but I'd keep it anyway. A gzipped mysqldump will be tiny in any case.
5. Have this event add summary data for the last day (say, Saturday at midnight), week, month, even year) to some summary tables.
6. Then delete from current_visit from Saturday at midnight - that way, you have at most (if you do this weekly) 700K records in your active current_visit table. This will make backups very quick - and inserts also.
7. You can then perform summary queries over days, months, years by summing over the the different tables for your dates of interest. You can also create VIEWs for those periods of interest.
8. Depending on your backup strategy and/or your other server activity, you can use your backups to take the summary data to another machine and perform historical queries at your leisure without stressing your production server.
9. Another use case is the ARCHIVE engine which is designed for non-updatable data - I know it's not your case, but it might be of use for others? I haven't used it, so I can't day - motto is test, test and test again!