Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 informations.

Two main operations are dense in project. First one is the SELECT operation on index page and second one 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?

share|improve this question

migrated from stackoverflow.com Apr 10 '13 at 2:06

This question came from our site for professional and enthusiast programmers.

3 Answers 3

up vote 9 down vote accepted

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/

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.