Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

is having millions of tables and millions of rows within them a common practice in MySQL database design?

I am doing database design for an upcoming web app, and I was wondering from anybody profusely using mysql in their current web apps if this sort of design is efficient for a web app for lets say 80,000 users.

1 DB

in DB, millions of tables for features for each user, and within each table, potentially millions of rows.

While this design is very dynamic and scales nicely, I was wondering two things.

  1. Is this a common design in web applications today?
  2. How would this perform, time wise, if querying millions of rows.
  3. How does a DB perform if it contains MILLIONS of tables? (again, time wise, and is this even possible?)
  4. if it performs well under above conditions, how could it perform under strenuous load, if all 80,000 users accessed the DB 20-30 times each for 10 -15 minute sessions every day?
  5. how much server space would this require, very generally speaking (reiterating, millions of tables each containing potentially millions of rows with 10-15 columns filled with text)

Any help is appreciated.

like image 446
roozbubu Avatar asked Mar 23 '12 19:03

roozbubu


People also ask

Can MySQL handle 1 million records?

The MySQL maximum row size limit of 65,535 bytes is demonstrated in the following InnoDB and MyISAM examples. The limit is enforced regardless of storage engine, even though the storage engine may be capable of supporting larger rows.

Which database is best for millions of records?

MongoDB is also considered to be the best database for large amounts of text and the best database for large data.

How many tables can you have in a MySQL database?

MySQL has no limit on the number of tables. The underlying file system may have a limit on the number of files that represent tables. Individual storage engines may impose engine-specific constraints. InnoDB permits up to 4 billion tables.

How do you store millions of records in SQL?

Another possibility is to create a time series in column oriented database like HBase or Cassandra. In this case you'd have one row per product and as many columns as hits. Last, if you are going to do it with the database, as @JosMac pointed, create partitions, avoid indexes as much as you can.


1 Answers

1 - Definitely not. Almost anyone you ask will tell you millions of tables is a terrible idea.

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

3 - Probably terribly, especially if the queries are written by someone who thinks it's OK to have millions of tables. That tells me this is someone who doesn't understand databases very well.

4 - See #3

5 - Impossible to tell. You will have a lot of extra overhead from the extra tables as they all need extra metadata. Space needed will depend on indexes and how wide the tables are, along with a lot of other factors.

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

like image 112
JNK Avatar answered Oct 31 '22 17:10

JNK