Re: Database design.. Asking again
On Mon, May 10, 2004 at 09:49:31PM -0700, Scott Haneda wrote: Sorry for the post again, I hijacked a thread and wanted to get this on the correct track. I can not seem to find the section in the manual that talks about the max number of tables MySql can use, can someone point me please? A classic hit is how efficient is your filesystem at handling large directories. Each table corresponds to a few files. Too many files in a single subdirectory can cause performance problems. I don't know if there's a limit at to how many tables can be handled by a single MySQL query. There are MySQL system variables that limit as to how big that query command can be. - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- Brian Reichert [EMAIL PROTECTED] 37 Crystal Ave. #303Daytime number: (603) 434-6842 Derry NH 03038-1713 USA BSD admin/developer at large -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database design.. Asking again
* Brian Reichert [...] I don't know if there's a limit at to how many tables can be handled by a single MySQL query. This limit is actually hardware architecture dependant: You can join 31 tables on a 32-bit server and 63 tables on a 64-bit server. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Database design.. Asking again
Man 3 times same thread ! What I could consider to you, I don't really understand what you are getting at, what is wrong with 1000 users firstly ? And in the entry table store their userID which is stored in a session when they login ? So when they enter in data it stores their userID into a column , is that right ? userID = autoinc userID in the user table -Original Message- From: Scott Haneda [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 11, 2004 2:50 PM To: MySql Subject: Database design.. Asking again Sorry for the post again, I hijacked a thread and wanted to get this on the correct track. I can not seem to find the section in the manual that talks about the max number of tables MySql can use, can someone point me please? I have been asked to build a database which could have some potentially interesting storage needs. There will be a users table, there can be x users, if all goes well, x will be 1000's. Each user will be able to upload any number of records, with 100,000 being the most. Average would be about 10,000 records, but I want to plan this as if average was 70,000. The 70,000 records will have the following structure: Id, first name, last name So the table will be relatively meager in its storage needs. Lets call this table user_contacts. 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? If anyone thinks I should just allow one table to store all this, with that table having 70 million records in it, then I can of course go that road as well. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database design.. Asking again
Scott Haneda wrote: I can not seem to find the section in the manual that talks about the max number of tables MySql can use, can someone point me please? I have been asked to build a database which could have some potentially interesting storage needs. There will be a users table, there can be x users, if all goes well, x will be 1000's. Each user will be able to upload any number of records, with 100,000 being the most. Average would be about 10,000 records, but I want to plan this as if average was 70,000. The 70,000 records will have the following structure: Id, first name, last name So the table will be relatively meager in its storage needs. Lets call this table user_contacts. 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? If anyone thinks I should just allow one table to store all this, with that table having 70 million records in it, then I can of course go that road as well. Another thing you may want to also consider when using huge databases (10M+); is the amount of time for searching and the overhead that would also cause. A with any search, regardless of the size of the database, it will take a small amount of time. Sure that amount of time would be around a couple seconds if even that. While a 10M record database could take significantly more. Which most likely that amount, may be even closer to about a minute or more. Now I don't know if a delay for searching the database would be a critical thing or not for you. Now of course, these time amounts won't be the same for every machine; nor are these time amounts be exact. Now if your are not in a time critical area, then you most likely won't care about this. Though, you may want to just do a simple query through a table, on your worst case environment; which from what you say may be up to about 70M records,and get an idea how much time it would take. Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database design.. Asking again
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: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
