On May 14, 2014, at 5:05 AM, Larry Martell <larry.martell@stripped> wrote:
> We have a table with 254 columns in it. 80% of the time, a very small
> subset of these columns are queried. The other columns are rarely, if
> ever, queried. (But they could be at any time, so we do need to
> maintain them.). Would I expect to get a marked performance boost if I
> split my table up into 2 tables, one with the few frequently queried
> columns and another with less frequently queried ones? Doing this will
> require a lot of code changes, so I don't want to go down this path if
> it won't be beneficial. Can folks here offer their experiences and
> learned opinions about this?
There are some advantages to splitting the table. If we use InnoDB as an example:
Storage is row-oriented:
- All those less-needed columns will by stored together in the same page (unless text or
blob - in which case it can be a pointer to an external page).
- This can consume more memory than required as the less important columns has to be
loaded with the important columns.
Locking is row-oriented:
- Having a non-normalized structure may mean more contention.
- One transaction is updating one part of a very wide row, another transaction is blocked
waiting to update a different column. This might not happen in a normalized schema.