MySQL多表与行

Question

I have read many posts on here about this discussion but my question is specific.

Please read in entirety before replying.

I am wondering whether it is best to have potentially hundreds if not thousands of rows in a database or to split it across multiple tables.

The scenario is: I have a user who can be in ONE AND ONLY ONE city at a time. (There are multiple cities e.g. Paris, Berlin and London) Now in each city are lots of areas (potentially several hundred). I was thinking that giving each city its own table would be more efficient.

E.G:
rooms_london
- All areas of london in here as rows
rooms_berlin
- all areas of berlin in here as rows

And so on for Paris and any other cities that I add in future.

Then in PHP I could construct a query similar to:

 SELECT * FROM rooms_$playerCity WHERE roomID = $playerRoom

Is this an efficient method or should I just add an extra column to a central rooms table.

If I've not been clear enough I will do my best to clarify anything that you need.

Many Thanks

Answer 1

I would not split rooms into different tables.

If performance becomes a problem I would use partitioning http://dev.mysql.com/doc/refman/5.5/en/partitioning.html

+------+          +------+
| City | 1 ---- * | Room |
+------+          +------+

whether ... thousands of rows in a database or to split it across multiple tables.

performance will not be a problem before few million rows with complex queries and insufficient indexing