How to partiton a table having 80 million records

[Vikas Jayna]

Hi,

 

I'm working for a matrimonial site and have been trying to find an optimal solution for the following problem:

 

I need to store the details of all the profile views happening on the site i.e. the id of the person who views the profile and the id of the person whose profile was viewed along with the date and a flag depicting whether the other person viewed back the profile of the person. Here's the table structure:

 

`VIEW_LOG` (
`VIEWER` mediumint( unsigned NOT NULL default '0',
`VIEWED` mediumint( unsigned NOT NULL default '0',
`DATE` date NOT NULL default '0000-00-00',
`VIEWED_MMM` char(1) NOT NULL default 'N',
UNIQUE KEY `UNI1` (`VIEWER`,`VIEWED`),
INDEX `UNI2` (VIEWED)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

 

The problem is that with around 300,000 views happening daily this table now has around 80 million records and inserts and updates on this table start getting stuck frequently increasing the load on the servers and slowing down the site.

 

One solution to above problem could be to partition the table into a number of small tables thereby reducing the cost of inserts but I need to run separate select queries on both the columns VIEWER as well as VIEWED and if I partition the table on VIEWER then the query on VIEWED will have to scan all the partitions which would be slower.

 

Any suggestions on how to manage such kind of tables?

 

Thanks for reading!

[bubblegum.anarchy]

Have a good long hard read and perhaps double read from the following link (I recommend a table for each day):

 

http://dev.mysql.com/doc/refman/4.1/en/merge-storage-engine.html

 

NOTE: I have never needed to and therefore never used The MERGE Storage Engine.

[fenway]

You may want to consider splitting your tables vertically.

[Vikas Jayna]

Yes! I will have to split the tables vertically. But is there a way out to avoid searching each of the split tables? I know it can be done if we are searching on only one field, but what if we are searching on multiple fields?

[fenway]

Why not create a summary table?