top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

MySql: Performance boost by splitting up large table?

+2 votes
436 views

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?

posted May 14, 2014 by Mandeep Sehgal

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

3 Answers

+1 vote

You can split the table into two and can avoid code changes by creating a view which matches what code is looking for.

I think loading few fields vs 254 into memory will make a difference but if your select statement only have specific fields you want and not the whole row (and also given the fact that you have index on the field you are using to search record), I don't think it can make a difference.

But I will looking forward for more answers to your question.

answer May 14, 2014 by Kiran Kumar
0 votes

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.

answer May 14, 2014 by Sanketi Garg
0 votes

You've already had some good advice, but there's something much more simpler that will also give you a significant boost: a covering index.

Simply put, the engine is smart enough to not bother with row lookups if everything you asked for is already in the index it was using. You'll need to keep the index requirements in mind, of course (most selective fields first, order fields after selection fields, etc) and then append any other fields to the same index.

Hard to say more without actual use cases, of course, but it's well worth looking into as it requires no other changes in application or schema.

answer May 15, 2014 by anonymous
Similar Questions
+1 vote
Mysql Innodb Performance - How to minimise multicolumn index?

Below table contains billion of rows,

CREATE TABLE `Sample1` (
  `c1` bigint(20) NOT NULL AUTO_INCREMENT,
  `c2` varchar(45) NOT NULL,
  `c3` tinyint(4) DEFAULT NULL,
  `c4` tinyint(4) DEFAULT NULL,
  `time` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`c1`),
  KEY `varchar_time_idx` (`c2`,`Time`),
  KEY `varchar_c3_time_idx` (`c2`,`c3`,`Time`),
  KEY `varchar_c4_time_idx` (`c2`,`c4`,`Time`),
  KEY `varchar_c3_c4_time_idx` (`c2`,'c3', `c4`,`Time`),
) ENGINE=InnoDB AUTO_INCREMENT=10093495 DEFAULT CHARSET=utf8

Four multi column index created because having below conditions in where

1) c2 and time
2) c2 and c3 and time
3) c2 and c4 and time
4) c2 and c3 and c4 and time

Cardinality wise c2, c3 and c4 are very low. (ex: Out of one million c2, c3 and c4 have 50 unique column in each)

Column time contains mostly unique fields.

Select, insert and update happened frequently.

Tables has 5 indexing fields(4 multi column). Due to this, 1) Insert and update on index fields become costlier. 2) As the table keep on growing (Nearly one billion rows), Index size also increase more rapidly.

Kindly suggest good approach in mysql to solve this use case.

0 votes
How to VIEW performance improvement using MySQL

I've a table with 10 Million records in MySQL with INNODB engine. Using this table I am doing some calculations in STORED PROCEDURE and getting the results.

In Stored Procedure I used the base table and trying to process all the records in the table. But it's taking more than 15 Minutes to execute the procedure. When executing the Procedure in the process list I am getting 3 states like 'Sending data', 'Sorting Result' and 'Sending data' again.

Then I created one view by using the base table and updated the procedure by replacing that view in the place of a base table, it took only 4 minutes to execute the procedure with a view. When executing the Procedure in the process list I am getting 2 states like 'Sorting Result' and 'Sending data'. The first state of 'Sending data' is not happened with view, It's directly started with 'Sorting Result' state.

When I'm referring some MySQL sites and other blogs, I have seen that VIEWS will never improve the performance. But here I see some improvements with a view.

I would like to know how VIEW is improving the performance.

+1 vote
What is the overhead in splitting a for-loop into multiple for-loops, if the total work inside is the same?

What is the overhead in splitting a for-loop like this,

int i;
for (i = 0; i < exchanges; i++)
{
    // some code
    // some more code
    // even more code
}

into multiple for-loops like this?

int i;
for (i = 0; i < exchanges; i++)
{
    // some code
}
for (i = 0; i < exchanges; i++)
{
    // some more code
}
for (i = 0; i < exchanges; i++)
{
    // even more code
}

The code is performance-sensitive, but doing the latter would improve readability significantly. (In case it matters, there are no other loops, variable declarations, or function calls, save for a few accessors, within each loop.)

I'm not exactly a low-level programming guru, so it'd be even better if someone could measure up the performance hit in comparison to basic operations, e.g. "Each additional for-loop would cost the equivalent of two int allocations." But, I understand (and wouldn't be surprised) if it's not that simple.

...