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
