hi,
i'm trying to build a virtual stock exchange site on a mysql database. the number of players will grow over time, so expandability and speed are important here (and anywhere else i guess 🙂. and i expect to have a lot of updates (read and write) to the database as players buy and sell.
to make one big table, i'll have all the prices, volume and one column to identify the users. so if i have 2000 users, average 20 shares, i'll be expecting 40,000 rows. a query will be something like 'select * from table_all where user="xx"'
to make many small tables, i'll create one table for each player, so the table will have columns like prices, volume etc, and without the 'user' column. if i have 2000 users, i'll get 2000 tables and maximum 20 rows each. a query will be something like 'select * from table_$user'
which one will better serve the purpose of the site, for now and in the future? i'm a bit worried what will happen when there are thousands of small tables, and on the other hand, worried about the performance when the query needs to seach thru few hundred thousand rows to find a few lines.
if i'm to use the 2nd option (many small tables), these tables don't have to be joined 'cos they will only carry info related to that particular user, and other info such as highest price, lowest price etc will be recorded to another table at the point of transation.
thanks
will.
