I am new to MySQL.
I am creating some web services using MySQL but I am not sure what is the best way to use the database.
One is the one database that has a lot of items and continually make row following query. Another one is making db database dynamically.
Database database is consist of some questions for each user and the number of questions is not fixed.
Dynamically creating new tables in database is a well known antipattern.
For one, it actually makes it more difficult to code, since you need to cater for dynamic table names in your query.
Second, and probably more importantly, it can be extremely detrimential on DB performance. Depending on which storage engine you use, whenever you query a table MySQL needs to open from 1 (InnoDB) up to 3 (MyISAM) files on disk. This will put a lof of strain on your filesystem, when you need to query many tables in short time.
On the other hand, if you have all your data in one table, MySQL will try to load it to memory when you query it for the first time, and all successive reads will be using memory access rather than disk access, which makes things a lot faster.
