First of all, changing action to INT(2) vs INT(11) takes the same space
{# inside ()'s is for display purposes not storage} on disk {I think
it's 4 bytes, but would have to look it up}. TINYINT or maybe even ENUM
{my preference} would only take 1 byte and would serve your purpose.
You could make details a 2nd table joined with the id field in earnings.
This complicates your code somewhat, but does save the space.
The size issue isn't a # of records issue but more of a max file size on
the OS of your server. If your server OS only supports a max file size
of 2GB and you are using MyISAM then you will hit this limit way before
you have to worry about # of rows. There is a max # rows variable in
my.cnf, but it is usually set very large.
You might want to consider using MERGE or UNION if you never delete any
records {See Manual}. This gets around the OS size issue and with
MyISAMPACK and MERGE could make queries against on the whole data set
run much faster.
Gordon Bruce
Interstate Software
A MySQL Training & Consulting Partner
> -----Original Message-----
> From: Daren [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, July 23, 2002 4:45 AM
> To: [EMAIL PROTECTED]
> Subject: Table Design
>
>
>
>
> 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 <mysql-unsubscribe-
> [EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
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
