MySQL performance - the number of Vs. tables Number of lines

Question

I have two routes,

1) creating sub-tables for each user and saving his individual content

2) creating several tables and saving data of all users in them.

eg.

1) 100,000 tables with 1,000 rows

2) 50 Tables with 2,000,000 rows

I want to know which route is the best and most effective.

Context: like Facebook, for millions of users, their posts, photos, tags. All this information is in some gigantic tables for all users, or each user has their own sub-tables.

Answer 1

Here are some of the pros and cons of these two approaches in MySQL.

1. Many small tables.

Cons :

• Additional used parallel tables require more file descriptors (check this )
• A database with 100,000 tables is a mess.

Pros

• Small tables mean small indexes. Small indexes can be loaded completely into memory, which means that your queries will run faster.
• In addition, data manipulations, such as inserts, will work faster due to small indexes.

2. Several large tables

against

• A huge table implies very large indexes. If your index cannot be fully loaded into memory, most queries will be very slow.

Pros

• The database (as well as your code) is clear and easy to use.
• You can use partitioning if your tables become so large. (note this ).

From my experience, a table of two million rows (I worked with 70 million row tables) is not a MySQL performance issue if you can load all your active index into memory.

If you have many concurrent users, I suggest you evaluate other technologies, such as Elastic Search , which seems to be better suited for this kind of scenario.

Answer 2

Creating a table for each user is the worst design. This is one of the first things you teach in the db design class.

Answer 3

A table is a strong logical component of a database and, therefore, is used for many maintenance tasks using a DBMS. For instance. usually set table file space, restrictions, quota, log space, transaction space, index tree space and much more. If each table has its own file for entering data, you will get long periods of time when connecting to tables or in general.

When you create many tables, you will have really BIG maintenance overhead. In addition, you will deny the very nature of relational sources. And just suppose you add a record to the database - each time you create a new table? It will be a little harder for your code.

But then again, you could try and see for yourself.