We're trying to figure out how to design a particularly critical table in our database schema. The choices are to use a single large table or a series of dynamically created small tables.
This table will receive the majority of traffic (queries and updates) in the database so it's a key part of the design. The data set means we're either looking at 1 table with perhaps 10 million records or 100,000 tables each with about 100 records. "Standard" SQL theory seems to say we should use a single table. It's more flexible and some queries simply aren't possible across multiple tables (or at least not efficiently). But in this case we're happy to live with reduced flexibility if it gives us substantially better performance. Early empirical testing with 100,000 records suggests the single large table becomes progressively slower to access as it grows in size (average access time goes from ~4ms/transaction up to around ~80ms for our test cases--MySQL 5.0 on CentOS). The multiple dynamic tables don't seem to have this property--access remains pretty much constant as you might expect (~4ms/transaction). So the question is, even given this 20x performance benefit are we still fools to consider the dynamic table model? Are we going to run into max-tables or max-file-handle limits or other problems that will eventually bite us? Or is this speed difference just an artifact of poor indexing choices or similar? Or are dynamic tables OK sometimes? Doug P.S. Here's the table in question: CREATE TABLE one_big_table ( rank bigint not null auto_increment unique, item_id int not null, user_id int not null, count smallint not null default 1, added datetime not null, primary key(rank, user_id) ) engine=InnoDB; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
