Does millions of tables and millions of rows inside them have common practice in developing MySQL databases?

Question

I am doing a database project for an upcoming web application, and I was wondering if any of you would use mysql in your current web applications if this kind of design is effective for a web application and allows 80,000 users to speak.

1 DB

in a database, millions of tables for functions for each user and inside each table, possibly millions of rows.

Although this design is very dynamic and scales well, I was interested in two things.

• Is this a common design in web applications today?
• How this will be done, in time, if you request millions of lines.
• How does a database work if it contains MILLIONS of tables? (again, in time, and is this possible?)
• if it works well in the conditions above, how can it work under a stressful load, if all 80,000 users access the database 20-30 times every time for 10-15 minute sessions every day?
• how much server space is needed, generally speaking (repeating, millions of tables, each of which contains potentially millions of rows with 10-15 columns filled with text).

Any help is appreciated.

Answer 1

1 - Definitely not. Almost everyone you ask will tell you that millions of tables are a terrible idea.

2 - Millions of ROWS are common, so just fine.

3 - Perhaps terrible, especially if the queries are written by someone who thinks it's ok to have millions of tables . This tells me that this is someone who is not very good at database knowledge.

4 - See No. 3

5 - Impossible to say. You will have a lot of additional overhead from additional tables, since they all need additional metadata. The required space will depend on the indexes and how wide these tables are, as well as many other factors.

In short, this is a very very serious bad idea, and you should not do this.

Answer 2

Millions of rows are perfectly normal use and can respond quickly if they are optimized and indexed correctly.

Millions of tables indicate that you have taken a big step in the way you created your application. Millions of rows times millions of tables times 80,000 users mean that, 80 quadrillion records? I strongly doubt that you have so much data.

Answer 3

Having millions of rows in a table is perfectly normal, and MySQL can easily handle this if you use the appropriate indexes.

Having millions of tables, on the other hand, seems like a poor design.

Answer 4

In addition to what others have said, remember that finding the right table based on a given table name also takes time. What time is it? Well, this is internally for the DBMS and probably not documented, but probably more than you think.

So, a query looking for a string can take:

• Time to find a table + time to find a row in a (relatively) small table.
• Or , just the time to find a row in one large table.

Most likely, (2) will be faster.

In addition, often using different table names in your queries makes query processing less efficient.

Answer 5

1] See dimension and fact tables in database design. You can start with http://en.wikipedia.org/wiki/Database_model#Dimensional_model .

2] Be careful with indexing too much: for high write / update you do not want to index too much because it is very expensive (think about the average case or the worst case of b-tree balancing). For high reading tables, index only the fields you are viewing. for example, in

select * from mutable where A ='' and B=''; 

you may want to index A and B

3] It may not be necessary to start thinking about replication. but since you're talking about 10 ^ 6 records and tables, maybe you should.

So, instead of telling you flat no for millions of question tables (and yes, my answer is NO), I think a little research will help you better. As for the millions of records, hints that you need to start thinking about “scaling” - as opposed to “scaling”.

Answer 6

If you are thinking of having millions of tables, I cannot imagine that you are actually creating millions of logically distinct tables. Rather, I would strongly suspect that you are creating tables dynamically based on data. That is, instead of creating a FIELD for, say, a user ID and storing one or more records for each user, you plan to create a new TABLE for each user ID. And then you will have thousands and thousands of tables in which everyone has the same fields. If this is what you are doing: don't. Stop.

The table should represent the logical TYPE of the object for which you want to store data. You can make a city table, and then have one entry for each city. One of the fields in the city table may indicate in which country the city is located. DO NOT create a separate table for each country in which all cities for each country are located. France and Germany are examples of “countries” and must go at the same table. These are not different things, a thing of France and a German thing.

Here's the key question: what data do I want to store in each record? If you have 1000 tables, all have the same columns, then almost certainly it should be a single table with a field that has 1000 possible values. If you are really serious about different information about France than about Germany, like in France, you need a list of provinces with a capital and population, but for Germany you need a list of companies with industry and a chairman of the board of directors, then everything is in order, it should be two different tables. But at this moment, the difference is most likely not in France, but in Germany, but something else.

Answer 7

SQL Server has many ways to support large tables. You can find some help by dividing indexes into several sections (filegroups), placing large tables in your own filegroup, and indexes for a large table in another set of filegroups.

A filegroup is basically a separate disk. Each drive has its own dedicated heads for reading and writing. The more disks, the more goals scans indexes at a time and, therefore, the faster the search results for your records.

Here is a page that details filegroups.

http://cm-bloggers.blogspot.com/2009/04/table-and-index-partitioning-in-sql.html