MySQL: 1 large table or hundreds of small ones?

[cameraguy]

Hello,

I developed a page counter and initially designed my database to have one table per counter. The tables record the traffic of each individual counter (daily visit of an ip address).

This tool is proving to be quite popular. I have already several hundred registrations and thus several hundred tables. I am now considering merging all the tables in one unique table.

Would a unique table (with thousands of entries) be too large for reading and writing queries from several dozens of simultaneous users? Is indexing by counter id enough to make this solution viable?

Thank you for your assistance.

Roberto

[mcibor]

Every table is three files in mysql, therefore it is easier for the disk to enter one file, than check hundred thousand (see copying of the music against copying dlls from window/system). Mysql is designed to operate on millions of records, so there shouldn't be any problems with that. BTW there are only few commands with which a query is not optimized.

I would go to one/two tables the most.

Best regards
Michal CIbor

[Lord Majestic]

Would a unique table (with thousands of entries) be too large for reading and writing queries from several dozens of simultaneous users?

You may run into locking issues as MySQL is not exactly the best database for multiple concurrent writers (reading is fine as it does not require serious locking).

I'd capture some stats first so that you know how many concurrent reads/writes you do. Writers are the real issue, it is possible that it may actuall make sense to keep 10-100 separate tables which is in effect manual partitioning of data to spread writers/readers like one spreads butter over bread.

[fischermx]

You may run into locking issues as MySQL ...

A tracker software will perform mostly inserts and selects.
Are there locking issues with insertions in MySQL?

[cameraguy]

Thank you all.

For the moment I have not observed or had reported any issues of the kind.

What about a monitoring/tracking tool? Can you suggest one?

Roberto

[anshul]

I think normalized-tables/many-tables/relational-db is good. And OO PHP code is good. Though, code must maintain integrity of db, as MySQL has no option like on delete cascade of Oracle. There'll b much improvements in future MySQL versions. I guess PHP 5 has more OO features than PHP 4.

[arran]

cameraguy, if you decide to go with one big table, i would use table locking (not page/row locking). this locking scheme is best suited to the nature of your table (heavy select/insert). also (as you pointed out), make sure an index is present on 'counter_id'.

if the performance degrades as the number of users increases, you may wish to consider using 2 tables - one for inserts and one for selects. you could then set up a conjob to merge the tables (using INSERT..SELECT) during a window where activity was minimal. This would mean that you could no longer offer real-time tracking but you could run the merge job several times daily. Another benefit would be the ability to speed up your tracking reporting by having multiple indexes on the 'select' table without incurring a performance overhead for the 'inserts'.