MYSQL: Should I create indexes on large tables used as logs?

Question

I have a basic analytics mysql database table which keeps track of all ipaddresses and urls visited on a user's visit and the time they visited. There are a large number of inserts (millions per day).

After a few days, running a query on the table to try to find out how many users visited on a particular day takes a very long time.

Should I add an index to the table? Will it recreate the index after every insert, and is it worth it?

Or is there a better way to speed up my analytics queries?

This is probably a common situation (everyone has logs). What is the best way to maintain this table?

Answer 1

Any table that will later be queried should use indexes. Look at INSERT DELAYED which returns quickly and is designed for logging:-

http://dev.mysql.com/doc/refman/5.1/en/insert-delayed.html

This is probably the best option for log tables, although you could also look at the Archive storage engine, but that is more concerned with space than lookups:-

http://dev.mysql.com/doc/refman/5.1/en/archive-storage-engine.html

Answer 2

MYSQL 5.0 introdcue Archive Storage Engine,
I believe that answer most your question

Should I add an index to the table
- yes, if your want to perform search

Will it recreate the index after every insert
- is handle internally by mysql

worth it
- depends, adding index will slow-down database write operation a bit (depending how many index and record length)

Or is there a better way to speed up my analytic queries
- if you just want to perform simple calculation, you can consider split the data daily (each table to hold a logging for each day). Or you can prepare some sort of query cache warm-up script