Freek.dev

Generated columns where introduced in MySQL 5.7. In the latest post on her blog Gabriela D'Ávila explains the feature.

There are two types of Generated Columns: Virtual and Stored. ... Consider using virtual columns for data where changes happens in a significant number of times. The cost of a Virtual Column comes from reading a table constantly and the server has to compute every time what that column value will be. ... You should consider using Stored Columns for when the data doesn’t change significantly or at all after creation,

https://blog.gabriela.io/2016/10/17/understanding-generated-columns/

If you like the post, be sure to check out Gabriela's excellent talk at Laracon EU as well:

Read more

Laravel 5.3, which will be released at this years Laracon US, has some new very handy functionality to work with MySQL 5.7 JSON columns. In a post on his site Matt Stauffer demonstrates the new API.

While Laravel has had the ability to cast your data to and from JSON since version 5.0, it was previously just a convenience—your data was still just stored in a TEXT field. But MySQL 5.7 introduced an actual JSON column type.

Laravel 5.3 introduces a simple syntax for lookups and updates based on the value of specific keys in your JSON columns.

https://mattstauffer.co/blog/new-json-column-where-and-update-syntax-in-laravel-5-3

If you're going to use this functionality, you should also read Mohamed Said's post on how to improve performance by using MySQL generated columns.

JSON columns cannot be indexed. You can work around this restriction by creating an index on a generated column that extracts a scalar value from the JSON column.

http://themsaid.com/laravel-mysql-json-colum-fast-lookup-20160709/

Read more

A few weeks ago Gabriela D'Ávila helped a famous guy with getting an MySQL import down from 16 to 6 minutes. In a post on her blog she explains how that was done.

A few weeks ago my friend Frank de Jonge told me he managed to improve an import into a MySQL server down from more than 10 hours to 16 minutes. According to him it had something to do with one of the field types (too long fields to really small data) and the amount of indexes and constraints in the tables. We were talking about 1 million records here. He wondered if it was possible to make it even faster.

...

Turns out there are many ways of importing data into a database, it all depends where are you getting the data from and where you want to put it.

Read more

Sure, you could always store JSON data in a TEXT or VARCHAR column, but having a native data type for JSON provides some major benefits over that approach: document validation and easy access.

Read more

I've used MySQL over the years and it has served me well. I didn't know the default behaviour is a bit messed up. Look at this video for some examples:

Because there's a lot of validation being done by the php-applications I work on, I've yet to run into the problems described above.Though the default configuration has some problems I'm not migrating my current projects from MySQL to something else. But for fresh new projects, I'll consider using PostgreSQL.

If you are considering this as well here's a guide by Chris Fidao (I stumbled upon the video above when reading that guide). If you are in any way interested in running your own server you have to subscribe to his excellent newsletter, Servers for Hackers, and buy the book with the same name.

Read more