Vikas Jayna Posted May 14, 2007 Share Posted May 14, 2007 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! Quote Link to comment https://forums.phpfreaks.com/topic/51278-how-to-partiton-a-table-having-80-million-records/ Share on other sites More sharing options...
bubblegum.anarchy Posted May 14, 2007 Share Posted May 14, 2007 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. Quote Link to comment https://forums.phpfreaks.com/topic/51278-how-to-partiton-a-table-having-80-million-records/#findComment-252583 Share on other sites More sharing options...
fenway Posted May 14, 2007 Share Posted May 14, 2007 You may want to consider splitting your tables vertically. Quote Link to comment https://forums.phpfreaks.com/topic/51278-how-to-partiton-a-table-having-80-million-records/#findComment-253076 Share on other sites More sharing options...
Vikas Jayna Posted May 15, 2007 Author Share Posted May 15, 2007 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? Quote Link to comment https://forums.phpfreaks.com/topic/51278-how-to-partiton-a-table-having-80-million-records/#findComment-253384 Share on other sites More sharing options...
fenway Posted May 15, 2007 Share Posted May 15, 2007 Why not create a summary table? Quote Link to comment https://forums.phpfreaks.com/topic/51278-how-to-partiton-a-table-having-80-million-records/#findComment-253522 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.