In message <[EMAIL PROTECTED]>, wrwatson2003 <[EMAIL PROTECTED]> writes >I know that I could certainly make this a heck of a lot easier. Of >course I could just create one table named "Products" and put all 90 >fields in that one table and be done with it. The part that bothers >me about this is the time it will take the database to retrieve the >records once that particular table has 40,000 records.
I have a table with about that number of records, correctly indexed, and it tells me: (1 row(s) returned) (0 ms taken) that is, it is so fast, it can't measure the time taken. Make sure that you have your table indexed correctly, and that the fields are as small as you can usefully make them. > Keep in mind >that there are going to be approximately 15 other tables in the >database that all work to make this Web-based program a success. Whether you keep this in 1 table, or in 4, there will STILL be 15 other tables, so this is irrelevant. > The >database will be working quite hard and I want to make it run as >efficiently as possible. Then put your 4 tables into 1 table (:-> >So, are you saying that it is better to have only one table instead >of four with my project? I'm confused by this because I used to work >with this guy that told me it is better to create more tables when >you have a lot of fields (e.g. 90 fields) with thousands (e.g. >40,000) of records that may be used as part of a multitude of >queries. If you are using something small like MS Access, he is probably correct. > He said that the database has to work much harder to filter >through all of the fields (e.g. 90 fields) in a single table than it >does to filter through multiple tables where the primary keys or >unique identifiers are the same. He told me that not a lot of people >are aware of this and it is a common mistake when creating databases. "Filter through" again? You mean retrieve the data? SELECT? Primary keys should be (will be) different. Tables slow when searching on fields where a lot of information is the same, as in Yes/No. How does having 4 tables help? You have to do four slow queries, instead of one. > >I'm not saying that you're wrong, but you didn't give me a reason as >to why one table is "practically" better than four for my particular >project. I understand that querying four tables seems like more work >than having to query just one table, and takes almost four times as long... >the guy that >told me how I should build my database has worked on major >programming projects for M.I.T., >Any suggestions? If you can tell me why only one table is better >from a "technical" standpoint, I will give it a shot. I have a suggestion - find someone who agrees with your friend... You must be aware that it is impossible to give exact timings on how long a search will take. This depends on the number of records, size and number of fields, speed and settings on your server, how much other work the server is doing at the time, transmission time over the internet, and many more variables, there are too many to mention. It's like politics, you won't get a definite reason from anywhere, you have to watch how many people vote for each idea, then decide who to follow. -- Pete Clark http://www.hotcosta.com http://www.spanishholidaybookings.com ------------------------ Yahoo! Groups Sponsor --------------------~--> Get Bzzzy! (real tools to help you find a job). Welcome to the Sweet Life. http://us.click.yahoo.com/KIlPFB/vlQLAA/TtwFAA/CefplB/TM --------------------------------------------------------------------~-> The php_mysql group is dedicated to learn more about the PHP/MySQL web database possibilities through group learning. Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/php_mysql/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
