mysql - Move frequently updated columns to separate table for better performance? -

(this same question here seeking specific clarification performance, not solving errors).

consider database table containing medium-to-large number of attributes (columns), of static or infrequently updated. table used in read operations extremely frequently.

if 2 or 3 of attributes (columns) are updated frequently, advisable split off separate table performance reasons?

one reason had understood benefit query cache table erased when table updated. in case, database mysql, table type innodb (row-level locking).

edit: 1 of updated columns not part of frequent reads, updated after maybe 25% of reads.

another of updated columns both part of frequent reads , updated each read, although may batch updates.

if have wide table and/or relatively narrow set of fields accessed more others, makes sense separate fields rest. however, instead of normalizing them own table, consider creating subtable within existing table. is, create covering index.

select  a, b, c    table   c, d, e; 

in simple case, create index on a, b, c, d, e. query may completed accessing index. same updates:

update  table     set d = 'something'   a, b, e; 

as long fields defined in index called out, index need used.

but never assume anything. develop timing tests can make meaningful comparisons before , after. develop regression tests can see if speed operations want doesn't unduly slowing down other important operations.

here's twist consider. take query above. suppose many thousands or millions of rows must examined (where clause) in order find few results. better create two covering indexes -- 1 selection list , 1 clause? , if is better (faster) should field c defined in selection index and index or in index?

i don't know answers questions. you'll have test.

update: if understand comment, have lot of queries fields , updates other fields , don't want them interfere each other since use different sets of fields. can "isolate" queries updates. query under "no lock" conditions.

in mysql, procedure (note: innodb only. refer docs other engines):

set session transaction isolation level read uncommitted; query1... query2... query3... set session transaction isolation level repeatable read; 

or simply

set transaction isolation level read uncommitted; query1... query2... query3... commit; 

you still may want @ covering indexes queries and/or updates, separate issue.