Strip your `TEXT` field

Gabriela D’Avila@gabidavila

Databases

Considerations

Doing an `ALTER TABLE`

• Creates a new table with the new structure

• Copies the data from the old table to the new table

• Renames the new table as the old table

Example

Alter PK from INT to BIGINT*

- 2.147.483.648 to

2.147.483.647

INT

- 922.337.203.854.775.808 to

922.337.203.854.775.807

BIGINT

**out of scale*signed

How long did it take?

Scenario

• > 750 GB

• > 380 milion records

• 3 `TEXT` fields

2 days

What is one the 1st things we learn when we start to

program?

Fast Slow

Why?

• 1 text file for field for every record

• The text files are stored in a different place than the table location

Engine

MyISAM

• Faster read speed

• Supports FULLTEXT indexes

• Non transactional

• Higher probability of data loss

InnoDB

• Transactional

• Data integrity

• Until the 5.6 version, MySQL didn’t support FULLTEXT indexes

Impacts of a TEXT field

• Inefficient search with the `LIKE` statement

• Slow `ALTER TABLE`

Alternatives

Faster Search

Faster Search

Faster Retrieval

CDN: Content Delivery Network

Keeping the TEXT field in your DB

Conclusions

• There is no right solution

• Some solutions may add another layer to the application

• Some solutions can only be applied in a low coupling system

Thank you!

@gabidavila

More information

• http://gabriela.io/blog/2015/04/27/strip-your-text-field/• http://www.percona.com/blog/2010/02/09/blob-storage-in-innodb/