Hi Daren,
How long do you need to store this information? The normalized table
that you describe is simple and easy to index. Having several million
rows in the table won't bother MySQL. So is it sufficient to keep the
data around for a month and then prune it out of the database? Long
term records could be dumped out into text logfiles for safe keeping.
Another possiblity is to invert your storage logic. By that, I mean
insert a row in the cross-reference table for every user that has
NOT read the email. Then when they read it, remove that row from
the table. This will automatically prune down the table size as
more and more of your readers check their email.
I would HIGHLY suggest using the DELAYED option on that size of an
insert.
You may not want to go this logic inversion route because of the havoc
it will play with other queries. For example, since any user that
does NOT have a matching row in the cross-reference table is assumed
to have read the message, any new user joining you will automatically
be assumed to have read all messages to date.
Rene
Daren Cotter wrote:
> I have a table which needs to store which emails each member has read and
> had their account credited for. Currently, I have a field "read_array
> longtext" in my main members table, and each time a member reads an email,
> it simply concats to the end of this array. For example, they read email
> 288, their read array is:
>
> 288
>
> They read 298, the read_array is:
>
> 288||298
>
> Using this method (which is far from normalization, I know), I have been
> able to accomplish the two major things I need to be able to accomplish:
>
> 1) Insure that no member can get credit for reading the same mailing twice
> (check their current read_array, using ereg())
> 2) Run a query to see how many members have read each mailing (WHERE
> read_array LIKE '%mail_id%')
>
> I know that both of these tasks would be easier if I created a new table,
> and stored the member_id, mail_id, and date, and the table would then be
> normalized...however, I send approximately one new mailing per day, and this
> would mean 100,000 new rows in this table every day. Can somone offer me
> "expert" advice as to which method is better? Using an array that will grow
> to unlimited size, or using a table that is going to grow much larger every
> day?
>
> Also, as a deterrant to switching to the normalized method...I frequently
> run queries to do the following:
>
> *) After I send a mailing, a week later, I send the mailing again to all
> members who did not confirm reading the email. My Query is like:
> select member_id, (more) FROM members WHERE read_array not like '%mail_id%'
>
> Is there any possible way I could accompish this task in one query if I had
> this process normalized?
--
Rene Churchill http://www.vtwebwizard.com
Internet Consulting 802-244-5151
Specializing in Web Programming
---------------------------------------------------------------------
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
