April 2023 Recap of Tobias Petry - Issue #20
MySQL EXPLAIN Explained
For the past months, I have been working on a research project that is now almost finished. The MySQL EXPLAIN output is unusable for complex queries: The output is displayed in the most uninformative way anyone could imagine:
Internal execution information is reported that only a minority of users may understand.
Some labels like Using filesort or Using Index are misleading.
And even the displayed information needs to be further processed. E.g. the rows column is an estimate for the number of rows per loop execution, not the total rows.
I've worked on an algorithm that can understand the generated EXPLAIN output and transforms it to be easier to understand:
Unimportant stuff is removed.
Barely visible information (e.g. index-only operations) is highlighted.
And so much more.
I will share more next month. Hopefully, with a working demo you can play with. Stay tuned ;)
SQL For Devs Newsletter
SQL For Devs is now 1 year old 🎉
I worked on a new newsletter design to celebrate still constantly writing new stuff (most blogs die within the first year). So the newsletter is not only looking more awesome. Every new issue will from now on also have some interesting articles at then end that I found the past week. And there will be more in the future…
Laravel Query Expressions
I’ve finished the time-consuming pull request #46558 for the Laravel framework to add escaping functionalities for query expressions. I worked on several different implementations until finally choosing this one as it needs the least amount of changes to Laravel. When it is merged the query expressions can be extended a lot. Stay tuned!
For the moment I worked on something really challenging. MariaDB, MySQL and SQLite don’t have functions to generate v4 UUID strings. And even PostgreSQL and SQL Server use different namings. I was able to build an expression to generate UUIDv4 for every database by doing a lot of math. You can now let your database generate a UUID for your columns.
Check out the package: tpetry/laravel-query-expressions
Laravel PostgreSQL Driver
The usual problem with ORM libraries is their ability to represent multiple databases. At first, this sounds great, but each database is different and to implement only the common features of all databases means to miss many specific functions. Since I primarily work with the Laravel framework and PostgreSQL, I started to develop an extended driver to support more features of PostgreSQL: tpetry/laravel-postgresql-enhanced
Last month these remarkable features were added:
My Database Tips
The database tips I am writing an entire article for are all collected on SqlForDevs.com. They are easier to share that way, and you can find them again more quickly than by looking through my old newsletters. You can also subscribe to the SqlForDevs newsletter to get these tips one day earlier than shared on social media.
Interesting Articles, News and Tools
I’ve moved the monthly links of interesting findings to my SQL For Devs newsletter. Compared to just once a month, I now share new findings every week!