How many tables does Mysql support?

Asked

Viewed 937 times

3

Well, what I want to do is create a table in my database for each user, now my question is: How many tables does Mysql support?

Will put enough tables my server does not lock or gives some bug?

Is that my site has close to 1000 users.

What they recommend to me?

Thank you.

mysql
  • 3

    I recommend analyzing the real need to have "a table for each user". I don’t know about the number of tables, because I think it depends a lot on the amount of reading and writing in your database. If it is too large the data transfer will need more processing on the server.

  • 3

    I’ve worked on systems with hundreds of thousands of users, all in the same table, but in several different rows in the same table. The system had a certain number of tables (about 40 or 50), but each of them was for a distinct purpose. Are you sure you are not confusing the concept of table with the concept of table rows?

  • Related: http://answall.com/q/139408/101

  • @Victorstafusa I believe that’s exactly what he wants. Distinct tables for each user. He seems very convinced.

  • For example, each user has his items, for this he needs to have a table, because each row of this table will be an item, Etenderam the idea?

2 answers

11

I don’t think your premise is a good idea.

Imagine that you want to add a column in these "tables per user". 1000 users would mean at least 1000 commands to run! What is a nightmare in both performance and administration.

If your goal is to separate content from users, I suggest:

  • Create a 'Users' table'
  • For each table to be separated, include a Foreign key for the 'Users' table'.

Thus, to fetch a user’s content would be simple as adding a WHERE:

SELECT pedidos.valor
  FROM pedidos
 WHERE usuarioId = 10;

That’s a lot easier.

  • What would be FK?

  • 1

    @Gonçalo Significa Foreign Key - points to a PRIMARY KEY in another table.

  • @Gonçalo Foreign key (Foreign Key) - It is what is used to create relationships between tables, so that values in one table reference lines in another table. It’s a fundamental concept to understand how a database works and how it should be used, so I recommend you research about it.

  • @Gonçalo, as Mauro commented. I edited to give more clarity.

4


According to the Mysql documentation, there is no table or database limit. The underlying filesystem may have a limit on number of directories.

Engines may have an individual value, such as Innodb that allows more than 4 billion tables.

What may occur is slowness due to the amount of existing tables, but limit does not exist depending on the storage engine.

Note also that this is bad practice, of course, depends on the application, but I see no need to do this, seeing that the programming language itself with a good modeling in BD can solve this more cleanly and efficiently.

See more in Limits on Number of Databases and Tables

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.