# New fields for large table that only impact a small subset of the data

**URL:** <https://forums.percona.com/t/new-fields-for-large-table-that-only-impact-a-small-subset-of-the-data/1424>\
**Category:** Other MySQL® Questions\
**Created:** [June 2, 2010, 10:54am UTC](https://forums.percona.com/t/new-fields-for-large-table-that-only-impact-a-small-subset-of-the-data/1424 "2010-06-02T10:54:08Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![iberkner](https://avatars.discourse-cdn.com/v4/letter/i/c67d28/32.png) [@iberkner](https://forums.percona.com/u/iberkner)\
**Post date:** [June 2, 2010, 10:54am UTC](https://forums.percona.com/t/new-fields-for-large-table-that-only-impact-a-small-subset-of-the-data/1424/1 "2010-06-02T10:54:08Z")

</div>

We need to add new fields to one of our tables but only a small subset of records will be impacted (or contain relevant values). I’m looking for some guidance about whether or not we should add the fields to the table or create a new table which will have a 1 to 1 relationship with the old table but only contain records where relevant data exists.

Some stats:

Current table has 2.5mm records, only about 50,000 records actually matter ( we may at some point clean it up ).

I need to add these fields to the table:

field1 ( varchar (40) )  
field2 ( int, indexed )  
field3 ( date )  
field4 ( enum (‘value1’ (default), ‘value2’, ‘value3’, ‘value4’) )  
field5 ( int )  
field6 ( date )

Only 50,000 records or 2.5% will ever have data in the new fields. Does it make sense to add them to the current table or create a new table with a 1 to 1 join relationship?

Any suggestions / help is appreciated.

Thanks!

---

<div class="post-metadata">

**Author:** ![sterin71](https://avatars.discourse-cdn.com/v4/letter/s/5e9695/32.png) [@sterin71](https://forums.percona.com/u/sterin71)\
**Post date:** [June 2, 2010, 3:13pm UTC](https://forums.percona.com/t/new-fields-for-large-table-that-only-impact-a-small-subset-of-the-data/1424/2 "2010-06-02T15:13:05Z")

</div>

Hmm, well since it’s so few rows that will contain the data you are right in suggesting to keep it in a separate table.

But then again if we say that each row increase with about 20-25 bytes with the extra columns then the total size of the table might gain between 50MB and 75MB.

So…, I think I would go with adding the columns to the existing table to keep the design simple, but you could do it either way and I don’t think you would really notice any difference performance wise (except if you don’t have a index on one of these extra fields, and you are forcing MySQL to perform a table scan of these fields, at that point a table scan of the 2.5% table and then retrieving the corresponding rows from the main table via primary key would be faster than traversing 100% of the rows where 97.5% is empty ) ).

---

<div class="post-metadata">

**Author:** ![iberkner](https://avatars.discourse-cdn.com/v4/letter/i/c67d28/32.png) [@iberkner](https://forums.percona.com/u/iberkner)\
**Post date:** [June 2, 2010, 3:15pm UTC](https://forums.percona.com/t/new-fields-for-large-table-that-only-impact-a-small-subset-of-the-data/1424/3 "2010-06-02T15:15:06Z")

</div>

Thanks for the response, we came to pretty much the same conclusion and are adding the fields to the existing tables.