2017.09.10 05:00 | Kurt Tomlinson
I learned this lesson the hard way, so you should learn from my mistakes. Don't put big fields like TEXTs or BLOBs in your main database tables. Doing so will result in a negative step change in the performance of your database one day when you least expect it. You'll think your database is humming along when suddenly your database queries that used to finish in a couple milliseconds start taking hundreds of seconds.
If you're interested in learning more about databases, I recommend Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design (3rd Edition). Either way, continue reading below.
What's the problem with putting big fields in your main tables? Well, if you do a query against a table, then MySQL loads every field in every row it looks at even if you're not planning to use all of the fields. This means that if you have a big table with big rows, then you're likely to need to load more data than you can fit in RAM. When that happens, your database server will start swapping to disk, and everything will grind to a halt.
How can you avoid this kind of thing from happening? It's easy. Just move all your big fields (TEXTs and BLOBs) into a new table. That way you can quickly sort and filter on your lightweight main table and then load in your heavyweight TEXTs and BLOBs from your secondary table as needed.
There's an excellent answer on Stack Overflow that relates directly to this: Speed of mysql query on tables containing blob depends on filesystem cache.
Did you find this blog post useful? Let me know in the comments!
Photo by Christophe BENOIT