Scott Haneda wrote:
<snipped>
>If users become day 1000 and each of those users has 70,000 user_contacts,
>that would be 70,000 * 1000 total records in one table, as users grow, this
>becomes perhaps too many records in one table. Or at least the potential
>for it.
>
>My next option would be to make a new table, user_contact-userid and make
>one for each user, would would then mean, rather than one table with a lot
>of records in it, there would be many tables with a max of 100,000 records
>in it.
>
>Can someone share with me their thoughts and suggestions on this?
>
>
<snipped>
MySQL has a table type called 'merge' tables for this purpose.
See http://dev.mysql.com/doc/mysql/en/MERGE.html
Basically you create a collection of MyISAM tables, and then define a
merge table which you can use to refer to all of them at once. You can
then either query the individual tables, or the merge table to get the
results you want.
As for whether this is necessary, that would depend on the type of data
you're storing. How big is each record? If it's just a couple of bits, I
think one table for everyone would be OK, even at 70,000,000 records.
However if your records are large ( eg contain text column, blob
columns, etc ) then merge tables might be the way to go, especially if
your data hits the 2GB limit.
In your case, with the fields ( ID, FirstName, LastName ), I think you
could get away with just one table. I haven't tried anything this big
though, so maybe someone who has can enlighten us both.
If you don't need any features of InnoDB, I suppose it wouldn't be too
much of a hassle setting up merge tables - just in case.
--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: dkasak@stripped
website: http://www.nusconsulting.com.au
