Strip your TEXT fields - Exeter Web Feb/2016

We use a TEXT field to store JSON, plain text and sometimes even HTML content. But 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` fields Gabi @gabidavila
2. about • Data Engineer • @Crowdcube
3. uses • serialised Arrays • JSON • large strings • images*
5. how does it work?
6. alter table •creates a temporary table with the new structure •copies the data from the old table to the new one •consolidates the new table
7. example
8. alter PK: INT -> 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 Illustration out of scale
9. case • > 750 GB • > 380 million lines • 3 TEXT fields • Auto increment: 898.191.090
10. how long did it take?
11. 2 days
12. why?
13. speed fast slow
14. storage • 1 file in the disk for each row for each TEXT field • stored in different location than the table data itself • each field up to 4 MB
15. engines
16. MyISAM • fastest read speed • supports FULLTEXT indexes • non transactional • less data reliability
17. InnoDB • transactional • better data integrity • until MySQL 5.6 it didn’t support FULLTEXT indexes in TEXT fields
18. querying into TEXT fields • inefficient search with LIKE statement • slow DDL operations (like alter table) • unnecessary increase of the table size
19. possible alternatives
20. fastest search
21. search servers • indexes large bodies • api bindings • decoupled
22. smart retrieval
23. content delivery network • high availability • cheap
24. TEXT field within the RDBMS
26. conclusion • there is no silver bullet solution • some implementations may add an additional layer of complexity to the application • some implementation works better with decoupled applications
27. thanks! come say hi :)