sql - MySQL: splitting one table to multiple tables (same columns) for performace increase? -

This question is about performance, not about possible solutions.

My system holds several items of different categories, each category has its own table because each table has several rows and the fields are different.

  ItemA - id, fld1, fld2 itemb - id, fld1, fld3, fld4 itemc - id, fld1, fld3, fld5 ....  

Now user inventory needs to be managed, i.e. whether the user has an item or not. An option is using a table:

  list - category_id, item_id, user_id  

category_id ItemA, ItemB, ... different for rows And how do we differentiate it.

The second option is:

  Inventory A - item_id, user_id inventory b - item_id, user_id ...  

The easiest to manage, but the inventory table is very large (the order of magnitude: the number of numbers of users of all categories) and repeatedly updated and frequently inquired.

The second option would be a bit difficult to manage (as we create a new list table for each category) but can offer a performance advantage because it can stop running conditions It is unlikely that any query inventory tables need more than one, because the categories are quite different.

The system currently uses the MySQL and InnoDB engines ~ 10 categories but is expected to grow up to a few dozen in the near future. In the largest category> 200 items are and most> 10k items are in single list table> 10m rows and more users are expected to be as much as joining.

I know that the best way to test the performance of both methods is to decide, but the truth is that it will not be

If you have a similar problem Along with personal experience , please share it.

Thanks

To normalize the database, generally better for performance and maintenance is.

Create a table item with this approach ItemA, ItemB, etc., which has 1: 1 relationship. You can then create a inventory table whose basis is related to item table. According to

, InnoDB supports line level locks, so there is no need to use several tables to prevent deadlock.