CODEWITHSUNDEEP
mysqldatabaseperformance
Mohammad Masoudian
Mohammad Masoudian

Reputation: 3491

one MySQL big table or many (performance)

I have a CMS that have 50 table in database.

I am working on a project that gives a copy of whole CMS to every users.

I have 2 solution for this :

adding a column called site_id to all of my tables e.g. users table

╔════╦═════════╦══════════╦══════════╗
║ id ║ site_id ║ username ║ password ║
╠════╬═════════╬══════════╬══════════╣
║  1 ║      20 ║ phoenix  ║ 12345    ║
║  2 ║      32 ║ sky      ║ qwert    ║
║  3 ║      44 ║ knight   ║ !@#$%^   ║
╚════╩═════════╩══════════╩══════════╝

create a separate table for each users e.g. users_20 table

╔════╦══════════╦══════════╗
║ id ║ username ║ password ║
╠════╬══════════╬══════════╣
║  1 ║ phoenix  ║ 12345    ║
╚════╩══════════╩══════════╝

I want to know which one of my solutions is faster and more efficient?

I want to do different work with my tables like Select, JOIN, SUM, ...

Upvotes: 2

Views: 55

Answers (3)

Pascal Le Merrer
Pascal Le Merrer

Reputation: 5981

In my opinion, solutions 2 looks better from a performance point of view.

With solution one, you will have tables with much more rows, so requests will be less performant. Mysql with have to lookup through a much larger number of rows when performing reading requests, or update bigger indexes when inserting new records.

Moreover the first solution will imply to modifiy the definition of the primary key and/or unicity constraints on some tables, to allow multiples items with the same name by example.

EDIT

A first question yu should answer is, will you really have a performance problem? It will depend on the number of user of your CMS. And you could make a quick benchmark to see if you CMS is slow with tables containing fake data for the number of users and artciles you imagine, or not. Then it would help you to consider which solution is better, taking in account pros and cons of each one.

Upvotes: -1

Gouda Elalfy
Gouda Elalfy

Reputation: 7023

if you can work with dynamic way with your database tables, like link every website with your users tables and when you create your website you create a table with same reference of your website name. this will be more faster because in this case you will divide all your rows in multiple tables ad this will be best for performance when you run the select queries, if you don't need to compare users website to any other - like if there is no problem if you have 2 users with the same username.

but if you have to check if user exit in any users table and shouldn't repeated at all so in this case I prefer putting all users in the same table because you will check for user in all tables in this will less performance.

also you should consider, when user login, which tables will check- are you know the website table or you must check user in all tables, if you can arrive to user table this will be better to use a multi tables if not you should use 1 tables and so on.

finally your requirements will guide you for suitable solution.

Upvotes: 1

bdifferent
bdifferent

Reputation: 703

The first option sounds much better for me. These are my arguments:

  • In the different instances for each user, you can use the same sql queries, and only need to change site_id
  • Reporting: If you want to report on any user cross function that will be a nightmare if you have separate tables for each user
  • Maintenance: if you have any new versions later on, you would need to apply DB changes to all different user instances (also nightmare)

You can argue, that if you have tables for all user, you dont need to filter by site_id, so it looks like better performance, but when you have an index on site_id in all tables you should not have any issues there. Thats at least my taking.

Upvotes: 3

Related Questions