Mysql – Should I separate frequently updated columns

database-designindexMySQL

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?

Best Answer

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/

Related Solutions

Mysql – What MySQL storage engine and table schema should I use

If you're updating frequently, you'll want innodb, which supports row-level locking. Your stats can go in a different table, which will be more efficient since it's not as wide as your 'feed' table.

Your data types look pretty good (unsigned, small/tiny).

I'm not a fan of your column names. Why not 'feed_id', etc.? Will you have no other "noun" in the system that could be abbreviated as "f"?

If f_cat is a foreign key, it should be cat_id.

There's basically no difference between a VARCHAR(50) and a VARCHAR(255), so you might think about giving yourself more room if you might need it.

Since f_lang is NOT NULL and only two characters wide, it's a small waste to make it a VARCHAR. I'd prefer to see it as a CHAR(2), which saves the lookup. Or normalize it and make it a foreign key over to a language table.

You've only indexes f_id and u_id. Is that really all you'll be coming in with? If not, think of composite indexes that match your where clause.

Hope that helps.

Should I create a separate user table for different web products within the same platform

A user is a user, which is different than a person.

A user is a role played by a person. A recruiter is also a role played by a person.

create table party(
  id serial primary key,
  name text not null
);


create table role_type(
  id char(1) primary key,
  name text not null unique
);


insert into role_type values 
('u', 'user'),
('r', 'recruiter');

/* use Table Inheritance to add role-specific details. */
create table party_role(
  party_id integer not null references party(id),
  role_type char(1) not null references role_type(id),
  ...
  primary key (party_id, role_type)
);

/*  create a user: */

insert into party values
(1, 'Neil');
insert into party_role values
(1, 'u');

/* create someone that is both a recruiter and user: */
insert into party values
(2, 'Tristan');
insert into party_role values 
(2, 'u'),
(2, 'r');

Best Answer

Related Solutions

Mysql – What MySQL storage engine and table schema should I use

Should I create a separate user table for different web products within the same platform

Related Question