First off, this post is going to be long :) I have designed a database that tracks member earnings on the site. Currently, the site has been operating for two weeks, has 10,000 members, and the earnings table already has 750,000 rows. I ran a query to see how many rows were being added each day, and it is growing exponentially. I.e., 5 days ago, 60,000 rows were added, yesterday, 100,000 rows were added. Before I explain why the table is growing so quickly, some background: Members are paid to read emails, click banners, etc. They are also paid when one of their direct referrals (a member who signed up under their referral URL) reads an email or clicks a banner, or when their indirect referrals (a member who signs up under one of their referrals' referral URL) reads an email or clicks a banner. Therefore, each time an email is read or a banner is clicked, 3 rows are added to the earnings table. This table also keeps track of misc earnings, such as signup bonuses, referral bonuses, etc. I designed it this way because if the site ever wants to add on (say, pay their members to search the web), the table does not need to be modified at all. So, now it's obvious why the table is growing so rapidly. So far, the size of the table is not a problem. The site is run on a dedicated server, so physical file size will probably never be a problem (maybe it will?). With 750,000 rows, the current file size is about 70mb. Also, since the table is indexed properly, queries are running smoothly, and I also do not think that should ever become a problem. However, data from this table will never be deleted, so it is just going to continue growing forever. With 100,000 members to the site instead of 10,000, this table will grow by millions of rows each day. My question: Can MySQL handle a table with tens or hundreds of millions of rows? Here is the design of the table: CREATE TABLE earnings ( id int(9) unsigned NOT NULL auto_increment, member_id int(8) unsigned NOT NULL default '0', action varchar(50) NOT NULL default '', details varchar(100) default NULL, amount decimal(7,3) NOT NULL default '0.000', time timestamp(14) NOT NULL, paid enum('N','Y') NOT NULL default 'N', PRIMARY KEY (id), KEY earnings_member_id(member_id) ) TYPE=MyISAM; I have played around with some possible "reconstruction" of this table to see the results. 1) Removing the details column alltogether cuts the physical size of the table to 70% of the original. Only about 5% of all the rows in this table use the details field, but the 5% that do use it absolutely require it. So this really isn't much of an option, but the 95% of rows that are not using this field are really wasting space. Is there some other way to accomplish this? 2) Modifying the action field from varchar(35) to int(2). Since there are only about 15 different actions (Banner Clicked, Banner Clicked by Direct Referral, etc), I could create a new table with all the actions, and then simply store the foreign key in the earnings table. This also cuts the physical file size down to 70% that of the original. However, physical file size (at least I think) is not really the issue here, the issue is the number of rows in the table, and neither of the above solutions help with this. The only other solution I have come up with, is the following: 1) Create a table with the following fields: member_id, # of banners clicked, amount from banners clicked, # of banners clicked by direct referral, amount from banners clicked by direct referral, etc, etc. This would require 12 fields just for banners clicked and emails read, and is certainly not a "good" way of doing things (I would think anyway). Then, I would have another earnings table in addition to this one which tracked misc earnings, such as signup bonuses, referral bonuses, and any other future addons the site may have. That way, I keep the advantages of what I have now, and I sacrifice little. (What I sacrifice is the ability to view a list of every single earnings credited to a members account. Instead, I only have the total # of banners clicked, and the total amount earned from that...I don't have a list of all banners clicked, etc). The method explained above would require a *LOT* of recoding, and I certainly don't wish to spend the time if it's not even the best way of doing things. If anyone has any ideas as to a good way of resolving this issue, I'd greatly appreciate it! I am fully willing to pay someone for consulting services if they can present to me a good solution to this problem! Filter: SQL, Query ------------------------------------------------ Join Excite! - http://www.excite.com The most personalized portal on the Web! --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php