Should I separate frequently updated columns?

Question

I have a users table that contains users' information and a column named credits that is frequently updated.

• On index page I'm showing list of users with basic user information but I don't need credits.

• On details page I'm also showing credits with user information.

Two main operations are dense in project. The first operation is the SELECT operation on index page. The second operation is the UPDATE operation on credits column. credits column is not indexed but since it's frequently updated, will it effect the indexed columns on users table? If so, I think I should separate credits column as a table and join it only when required. Doing by this, can I improve SELECT performance on index page?

Also, I'm using MySQL. What kind of table structure should I use for frequently updated columns?

Answer 1

It seems what you want is a so-called Index-Only Scan for the query on the "index" page. That means that you have all the data (columns) that you select on the index page in the index you are using for this query. This makes it unnecessary to go to the table when executing this query.

Example:

SELECT name, basic, information
  from users
 where filter = something 

(I just suppose you are using some filter).

If you create the following index:

 ALTER table users add index (filter, name, basic, information);

MySQL can execute the above query without actually reading anything from the table (just the index—hence, Index-Only Scan). In other words, the number of columns in the table does not affect the performance of this query.

You can recognize an Index-Only Scan by the presences of "Using Index" in the Extra column of the execution plan (explain).

If you update columns that are not part of this index, the index doesn't need to be updated.

Some links to my site http://use-the-index-luke.com/

• Index-Only Scan (aka. covering indexes)
• Update performance
• Getting MySQL execution plans & Operations

Answer 2

If users to credits is a one-to-one relationship, then you can keep the credits in users table itself. I do NOT think it will affect the performance of SELECT query in the index page. You can just fetch the columns you want. Normalizing the table is good, but if you do normalize for all the columns like this, the performance will be much slower when you do joins on all these tables.

Answer 3

Yes you are right. You should separate credits column from the table in order to make detail work efficiently. And try not to make indexes of frequently used tables.