A Million Tables
The question was asked if one could create a million tables in a MySQL database. So I put together a small php script to do just that. It ran all afternoon, all evening, and into the night. It hung during the creation of the 262,731st table. I was running Windows Server 2003, using the NTFS file system on a mirrored volume. Each table was an InnoDB table with a single tinyint field and no indexes. The ibdata1 file grew by more than 3GB during the process although no rows were ever inserterd. Windows file compression is enabled, however and the 4.4GB ibdata1 file actually occupies only 295 MB.
I posted back to the fellow after my script had run for several minutes my calculation that it would take about 27 hours just to create the tables and he might want to rethink his design. I never heard back from him.
Recently another forum post asked a question in a similar vein, about creating "more than a couple thousand" databases. I suspect a rather similar answer would be obtained.
People ask too, about how many columns they can create. People always want database to be like spreadsheets, and keep thinking in the wrong terms. The power of a well-tuned database lies in the simplicity of the schema; the fewer databases, tables or columns the better, and instead efficiently storing as many millions of rows as necessary.
I posted back to the fellow after my script had run for several minutes my calculation that it would take about 27 hours just to create the tables and he might want to rethink his design. I never heard back from him.
Recently another forum post asked a question in a similar vein, about creating "more than a couple thousand" databases. I suspect a rather similar answer would be obtained.
People ask too, about how many columns they can create. People always want database to be like spreadsheets, and keep thinking in the wrong terms. The power of a well-tuned database lies in the simplicity of the schema; the fewer databases, tables or columns the better, and instead efficiently storing as many millions of rows as necessary.
posted by Bob Field at 12:20 PM
2 Comments:
Hi there,
I'm currently writing a program that statistically tracks 100's of thousands of chess games. I need some schema advise.
Currently, I am creating a table with a name that uniquely describes the board layout. The records of each table consist of the different moves and the endgame success these moves brought i.e. win, lose or draws.
As different games can experience the same board layout (and more so at the end of the game when there are fewer pieces) I must search for an existing table before creating a new one. This searching is of course too slow.
Is there a way of indexing tables? I'm using the tables as records. Should I just make one giant table with the board positions as the primary key? If so, how do I add an undetermined amount of moves in that record? That would be spreadsheetish wouldn't it?
I'd appreciate any help. THanks buddy.......
lance_cotton@shaw.ca
Instead of hundreds of thousands of tables, make just one table. Add a column containing the text that you were using to name your tables. For more help and advice please post your questions on the MySQL Forums where myself and others will be glad to offer assistance.
Post a Comment
<< Home