I have a table containing the list of visitors and this table has the following information.
- Visitor Browser Information
- Visitor Location Information
- Visitor Time Information
- No of Visits
I have a second table that maintains the history of each visits, which means I if the same visitor visits the site, I insert into the second table and update the no. of visits on the first table.
The kind of reports that I have to generate for this table are
- Count of Visitors/day or days (Search Between days)
- Count of Visitors/month
- Count of Visitors/year
- Count of Visitors/browser or grouped by browsers
On an average there are about 20000 inserts to the second table and about 15000 inserts to the first table, meaning 5000 were updates to the first table (5000 repeat visits).
I need to decide between partitioning the tables by month and sub-partitioning by days for the reports 1,2,3 and index the browser related columns for report 4.
There will be more reports in the future not sure on what clauses.
Does partitioning/sub-partitioning along with indexing help in the performance of inserts and selects?
Should I perform partitioning on both the tables?
I am currently using MySQL 5.5 + InnoDB