List:General Discussion« Previous MessageNext Message »
From:Morgan Tocker Date:May 14 2014 2:47pm
Subject:Re: Performance boost by splitting up large table?
View as plain text  
Hi Larry,

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.

- Morgan
Thread
Performance boost by splitting up large table?Larry Martell14 May 2014
  • Re: Performance boost by splitting up large table?Sukhjinder K. Narula14 May 2014
  • Re: Performance boost by splitting up large table?Morgan Tocker14 May 2014
    • Re: Performance boost by splitting up large table?Johan De Meersman15 May 2014
      • Re: Performance boost by splitting up large table?Larry Martell15 May 2014
        • Re: Performance boost by splitting up large table?Johan De Meersman15 May 2014
          • Re: Performance boost by splitting up large table?Larry Martell16 May 2014