Will my database choke up if...

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
$var
Forum Contributor
Posts: 317
Joined: Thu Aug 18, 2005 8:30 pm
Location: Toronto

Will my database choke up if...

Post by $var »

Can anyone tell me some negatives about designing a relational database in MySQL that has the potential to grow to 100,000's of entries.

I work for an events company, and each event has speakers, and sponsors, and they all belong to different industries and business units... which means, a lot of cross checking.

for example, i am going to set it up like this, all these ID's relate to the 1 event:
Event_ID
Event_UnitID
Event_IndID
Event_SubIndID
Event_VenID
Event_CityID

Spk_EvntID ()

Sponsor_EvntID ()

Is this going to be too much in the future? Should I be worried that by splitting up all the information into different table, and using
SELECT * FROM ### WHERE ID=$ID;

I'm sort of novice as far as DB building goes... and I don't want it to explode on itself in 2 years after it has to look through 10000 records to find the Event_ID the Spk_EventID corrosponds to?

Or is that just how it's supposed to work, and that's what databases are built to do? Is SQL sturdy enough to handle it?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Hundreds of thousands of records is not even remotely a problem for most databases.
User avatar
$var
Forum Contributor
Posts: 317
Joined: Thu Aug 18, 2005 8:30 pm
Location: Toronto

Post by $var »

awesome... i know that it seems petty to worry that much data, because that's not even really a gig to sift through...
i was just looking for some input on how i was going to set it up, and if that looks functional.

i sort of learn from looking, but still, don't have any coder buddies to let me know if i was looking correctly.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Databases are built to use JOINs, it's a relational nature. As long as your design is fairly normalized I doubt you will hear much from most of us other than "looks good."
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

I agree with feyd. You typically want to set your tables up in a way that keeps data specific to a particular item in that item's table. Relate that table to others as needed.

Just make sure you tap into indexing and development of your table's keys. This makes searching a lot faster on larger tables. Also look into the various table types (InnoDB, MyISAM, Heap) to see which would be the best suited to your needs.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

Everah wrote:Just make sure you tap into indexing and development of your table's keys.
Mysql can help you to some degree to improve your tables/indices/querries. Take a look at http://dev.mysql.com/doc/refman/5.0/en/explain.html
Oversimplified: whenever a EXPLAIN SELECT ... tells you "type:ALL" and/or "possible_keys:NULL" you certainly did something the wrong.
Post Reply