Mysql – Will Partitions and Indexes on the same table help in performace of Inserts and Selects

indexinnodbmysql-5.5partitioningperformance

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

  1. Count of Visitors/day or days (Search Between days)
  2. Count of Visitors/month
  3. Count of Visitors/year
  4. 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

Best Answer

May I suggest the following strategy:

Since there is a limit of 1024 partitions and sub partitions in single table have a Current table (1-2 months of data) partitioned by day and browser. And another History table for archive partitioned by month/day.

If you need to run reports on browser split, then have several tables with day/browser key.

Although there will be a number of tables (you may keep e.g. a table per quarter and then have second line of archiving to a table with month/browser key) it will be manageable, because partitions are quickly switched from one table to another.

Non-clustered indexes always help your reports, so having an index on a often used column is a good idea whatever partitioning strategy is implemented.

Depending on your reporting requirements you may also consider creating a summary table, which will have data already pre-aggregated for your reports. It can be populated overnight and speed up your reports dramatically (indexes is a must on this table too). You can even populate this summary table several times during the day without locking the table by using READ UNCOMMITTED - it is unlikely that you need precision to every visitor.

Related Question