How many MySQL database tables can cause bad effects?

Recently, to help the school to do a questionnaire system, the current storage questionnaire and the results of the questionnaire are as follows:

For each additional questionnaire, the database adds two tables, one of which is used to hold the information for the questionnaire, and the other to hold the information of the questionnaire results.

Did not contact the database before, before doing this system to check some information about the database, see someone said

The number of tables will not affect the query speed, but may be because your data storage structure more organized, but query more efficient

After this system has been running for a while, I feel that the above statement seems less plausible, and is now preparing to redesign the structure of the database. Before I do, I would like to ask you familiar with the database predecessors: the number of database tables as I have just described the dynamic increase will be what the drawbacks? What kind of rules does the structure design of a database generally follow?

20140530 17:10 New
Thank you for the answer, everyone's answer is to answer my doubts, and I would like to explain, I am not the main question is to ask me this database how to design, but focus on the data table too much will have what kind of result .
Thank you again for your reply:P

Reply content:

Recently, to help the school to do a questionnaire system, the current storage questionnaire and the results of the questionnaire are as follows:

For each additional questionnaire, the database adds two tables, one of which is used to hold the information for the questionnaire, and the other to hold the information of the questionnaire results.

Did not contact the database before, before doing this system to check some information about the database, see someone said

The number of tables will not affect the query speed, but may be because your data storage structure more organized, but query more efficient

After this system has been running for a while, I feel that the above statement seems less plausible, and is now preparing to redesign the structure of the database. Before I do, I would like to ask you familiar with the database predecessors: the number of database tables as I have just described the dynamic increase will be what the drawbacks? What kind of rules does the structure design of a database generally follow?

20140530 17:10 New
Thank you for the answer, everyone's answer is to answer my doubts, and I would like to explain, I am not the main question is to ask me this database how to design, but focus on the data table too much will have what kind of result .
Thank you again for your reply:P

Don't have more than hundreds of tables. There is little performance problem--personal opinion, no basis.

However, if you have only a few rows of data per table, or the amount of data will not increase, this design is a misuse of database tables, you can refer to the following ways to optimize:

• Create a questionnaire and a table of results, so that there are only 2 tables in total.
• Store questionnaires and results in json/xml or other similar formats, because the structure of different questionnaires is mostly different and the query characteristics of database tables are not used

Reference:

• What happens to MySQL when you have 200,000 tables in a database?
• Is there a limit to the number of tables in a library in MySQL? Does too many tables affect the performance of your data? For example, to split a table into 1024, what is the impact on the performance of each table? What factors need to be considered in these impacts?

There is a lot of discussion online, you can see for yourself.

Overall, there are too many tables, one is the efficiency of the file system will be reduced, and the second is the difficulty of maintenance increased.

It is recommended that, depending on the actual needs, if you have a maximum of one survey per day, then either option will be tolerable.

The table basically follows a few paradigms that we often say, but we cannot blindly follow this, because in practical terms, these paradigms sometimes become burdensome! However, is not to say that do not follow, that is, we meet their own needs and ensure the performance of the system, as far as possible to meet the paradigm, after all, the previous summary of things exist so long, there is a merit!
Second, the database table is not the more the better, at some level, the table does reflect the details of your database, but from the level of your query, you are in the query of the corresponding slow, and may be due to your negligence, produce a lot of dirty data, but if in order to ensure the integrity of the data, Association seems to be a good choice, but it can be clearly said that the association of the table has an impact on the performance of the database, so according to their own requirements, choose the right is the best! ...... Don't spray in your humble opinion!

A questionnaire database generates 2 tables design seems to be problematic, unless it is a very special requirement, the normal survey database is generally designed like this:
1. User (Users table)
Id,uesrname,password,email
2, Questionnaire (questionnaire, used to record questionnaire attributes, such as title, Introduction, start and deadline, etc.)
Id,title,intro,type,start_date,end_date,create_date,update_date
3, Question (List of questions under the questionnaire)
ID,QUESTIONNAIRE_ID (a questionnaire), type (such as single or check, or other input, etc.)
4. Option (Options table, options to save each question)
ID,QUESTION_ID (owning problem), label (tag), value
5, Vote (poll results, used to save the results of each user's selection)
ID,QUESTION_ID (For questions), user_id,option_id (the option that the user ultimately chooses)

The end is between 5~6 tables.

I'm not quite sure why you have to split each questionnaire into two tables, and I think maybe you don't understand the concept of tables and rows. A piece of data, note is a copy, put in the database is also a row, more complex data structure, placed in the database, is nothing more than a number of related tables in the row, there is no need to create a new table for a piece of data. Moreover, the database table how much of itself has little impact, mainly in the process of operation, more trouble, it can be said that the table should be used to query the questionnaire, you put in the program with code query, no use of query statements.

We did a design before, is to take user data by ID modulo, split into 100 tables, post-processing time is really very troublesome. Later only to understand, in fact, hundreds of thousands of millions of data, simply do not have to dismantle so many of the tables, the general small and medium-sized systems are not up to the bottleneck of MySQL, many times is too much to think

Depends on the exact engine you use.
MySQL InnoDB words, if do not do extra settings, the table is in a space inside, there is no file system restrictions. However, InnoDB will log table and partition information in memory and not be actively freed, and if too many tables are likely to run out of memory (millions of tables are estimated).

Other engines, if the table is independent of the file storage, it is limited by the operating system files

