拥有超过一百万个表的数据库有哪些危险？ [关闭]

Question

Can a MySql database handle over a million tables (mostly small, less than 10 rows)? I started a project that will store lots of literature, each sentence being stored in a different table consisting of about 2-10 rows. Here are some of my concerns.

1. Will performing SELECT and INSERT queries in this database be slowed down? How much?
2. Do website hosting services set a limit on the number of tables in a database? Or is there a universal limit for everyone?
3. Anything else I should be concerned about?

I hope this is the right place to ask this question. Thanks.

edited:

I knew the "why" questions would come! I am crowd-sourcing translation work. My current plan is to have each sentence of a document its own table. Each row of the table will have the data for each suggested translation. For example: table "documents" will have a list of the documents being worked on. table "document_1" will be the text of one document, each row conaining one sentence. table "document_1_1" will contain each suggested translation.

I am open to hearing a better plan :)

Answer 1

No, mysql does not have a limit to number of tables in a database. Although, obviously, you will be constrained by how much disk space you have available.

Answers:

1. It can be very slow. Slow, that it may start hanging.

2. I have just seen limit on number of databases, which is a marketing tactic. I don't know about the number of tables.

3. It is generally considered a bad practice to have a database schema where the number of tables grows over time. Strongly suggest to Redesign the database.

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.

From what I understood, you can have a table with these four columns:

1. A primary key - bigint
2. Name - text size 100, this can be your current table name (document name)
3. Description - max text size, the data you have in table goes in this column (document text)
4. Translation - max text size, the translation goes in this column (translated text)

Your column two above can be non-unique and repeat as per the number of rows in the table (2-10 times).

For more help and advice please post your questions, I know many people here are curious to know what you are cooking here.

PS. Not a mysql person, I am sure you know what datatypes to use