Large number of entries

[expert01]

Is it better to have 100,000 tables with 10 entries each, or one table with 1,000,000 entries? Please take this as a serious question for performance, I may soon encounter a situation not only like this, but with many more entries.

 

[vacunita]

It depends largeley on the type of information, and the type of relationship each table has to each other.

You would not store product information in the same table as Client Information. But you could store Teacher info in the same table as student information. And have a boolean field that defines them as student or teacher.



----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[KarveR]

My largest tables runs around 30million records at approx 14gb of space, this isn't optimal but queries on avaerage are 0.02 seconds so it doesnt really bother me.

Table size perfomance is directly related to OS and hardware setup.

Obvioulsy I couldn't do this on a 386 runing windows, so you need to include more info to be able to tailor advice for you.

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[expert01]

I want to have one database for all my website information, and one database that has users and products they've purchased.
I was thinking of:

->Purchased
-->User1
--->Product1: Yes
--->Product93: Yes
-->User2
-->User3
--->Product64: Yes

with each user being a table (with a name of the user's id) and each product being an entry (each entry being sorted by the product ID) with whether or not the product has been purchased (0/1 instead of true/false). This is for a database that I expect to get hit several hundred thousand times every five minutes (assume the server can handle it).

 

[guelphdad]

that is horrible database design. My only advice ... stop considering it!

have a single table to hold users.
have a single table to hold products
have a single table to hold users and products with a row for each product a user has.

user table
id name
1 Vern
2 George
3 Sam

Products table

100 tools
101 soap
102 car
103 house

users and products table
1,100
1,101
2,100
2,103
2,102

this is scalable into the millions of rows. and properly tuned database and indexed tables will not tax your system at all.

 

[expert01]

so it's more wise for MySQL to have one big file in the 20GB range than a ton of smaller files?

 

[guelphdad]

It is wiser to properly normalize your database rather than not.

 

[johnwm]

expert01
You can learn the basics of relational db design here:

http://www.alphabetcitydataworks.com/fundamentals.htm

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[expert01]

I'll do it my way. Maybe it doesn't seem the best from a "classical" point of view, but from a file system point of view it is much better. Easier to access a single entry in a 200KB file than in a 30GB file.

 

[JCreamerII]

It's always better to have a quality database design. guelphdad has it right, his model is a properly normalized model of the example you noted. A poor database design will slow you down today and kill you tomorrow. It's important to understand the concepts behind normalizing a database and the pros & cons to tweaking the design to be completetly normalize or not. There my be reasons to not fully normalize any design but the closer you get to proper normalization the better off you will be today & tomorrow.
Just Google the topic & you'll find alot of references to peruse.

Thanks,

 

[guelphdad]

I'll do it my way. Maybe it doesn't seem the best from a "classical" point of view, but from a file system point of view it is much better. Easier to access a single entry in a 200KB file than in a 30GB file.

If you have already decided to do it your way then why did you bother to post the question in a forum?

Just because you don't understand how/why something works doesn't mean it is wrong because it isn't the answer you want to hear.

 

[TonyGroves]

Easier to access a single entry in a 200KB file than in a 30GB file.

No, it's not. If the database is properly structured and tables properly indexed then queries will be generally simpler, performance generally faster, and administration definitely easier. The only reason you might want to compromise your database design by splitting tables unnecessarily is if your filesystem and utilities are genuinely likely to have a problem with big files; but even then your proposed design is not the way to do it.

 

[JCreamerII]

Hopefully this is your own app and database because this is the kind of situation that can drive the next person who has to work on it to pull their hair out. If you have a problem running your query now what is it going to be like with 10 times as many records as now, 100 times as many as now. If your getting an inconsistent result now, how bad will it be later?