MySQL databse/table question

agulaid

I have 232 countries i will have listed in a database, would you say it is more efficient to have all of these countries in one table or separate tables? -- this is from a performance viewpoint.

My other question is does a MySQL database have a limit interms of the amount of possible tables within a database?

It would be great to get some feedback.

Piranha

I am curious. If you won't have them in one table, how would you have them? Since databases are built to handle millions of records without any problem I would say that it is no preformance issue to have a few hundred rows. Performance issues are much more about doing the queries correctly and having the right indexes than the number of rows in the database. Not that you need to worry about that either with only a couple of hundred rows. Take this board as an example. All the posts are stored in the same table, and the id is over 10 million. Ok, some posts gets deleted, but still there are probably more than 5 millions rows without any big performance issue.

To your second question the answer is no.

I am wondering what you are developing that could be that performance critical and need that many tables. Please tell.

agulaid

Well its a huge property database that im designing for my company, im new to php and though id give it a shot. it'll have property from every country in the world.

Sorry i didnt explain it correctly. My question shouldve been:

Would it be better to store a whole bunch of records millions in size in one table, or spanning multiple tables as the records can be sub divided into country tables.

tomhath

As Piranha said, with proper indexing a few million rows shouldn't be a problem.

Keep in mind what else the database will be used for. That amount of data should be very valuable to the company; they'll probably want other reports or applications. I think spreading the rows across hundreds of tables would make it hard to use elsewhere.