Strip your TEXT fields

Unconference track of PHPNW 2015

We use a TEXT field to store JSON, plain text and sometimes even HTML content. Buy why this kind of field is so prejudicial to your database? What can we use instead to have the same flexibility? And if it can't be avoided, what can be the best solution to using it?

Transcript

1. Strip your `TEXT` field 
 
 
 Gabriela D’Avila @gabidavila
2. Databases
5. Considerations
6. 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
7. Example
8. 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
9. How long did it take?
10. Scenario • > 750 GB • > 380 milion records • 3 `TEXT` fields
11. 2 days
12. What is one the 1st things we learn when we start to program?
13. Fast Slow
14. Why? • 1 text file for field for every record • The text files are stored in a different place than the table location
15. Engine
16. MyISAM • Faster read speed • Supports FULLTEXT indexes • Non transactional • Higher probability of data loss
17. InnoDB • Transactional • Data integrity • Until the 5.6 version, MySQL didn’t support FULLTEXT indexes
18. Impacts of a TEXT field • Inefficient search with the `LIKE` statement • Slow `ALTER TABLE`
19. Alternatives
20. Faster Search
21. Faster Search
22. Faster Retrieval
23. CDN: Content Delivery Network
24. Keeping the TEXT field in your DB
27. 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
28. Thank you! @gabidavila
29. 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/