[5.x][MariaDB] add Field::getValueSql Event / support virtual columns / how to increase query performance

[Anubarak]

We usually use MariaDB (not my decision) and the upcoming Craft 5 content changes will cause heavy performance issues for some projects unless we do things we really should not do (eg: create a regex over SQL / overwrite all fields / overwrite the ElementQuery class) since MariaDB does not allow to create indexes in JSON as easily. (please correct me if I'm wrong - nothing worked for me, in case there is a way I'll be happy)

To deal with this, we tried to create virtual columns and include an index on these

ALTER TABLE `elements_sites` ADD `my_custom_index` TEXT AS (cast(json_extract(`content`,'$.\"0f071920-8bce-4807-8065-a6962a55f577\"') as TEXT)) VIRTUAL;

This allows us to keep a higher performance, however all field Queries must now create their where condition on the newly created virtual column instead of the real one.
It would be awesome if there was an event or settings or config value to modify the Field::getValueSql to the good old fashion way.

The ultimate solution would be a config file that could map fields with their indexed column so we could add indexes on the fly without the requirement to sync project configs.

[brandonkelly]

MariaDB support is going to be deprecated in Craft 5. There have been numerous performance and stability issues with it, and its lack of proper JSON support was the last straw.

We’ve worked around the JSON limitation just to ensure Craft still works with Maria, but I don’t see us going this extra step, when you would be better off switching to MySQL which fully supports JSON and functional indexes.

[Anubarak]

Sorry to bother you again: is the plan still the deprecation of MariaDB?
I am just asking because you updated your server requirements craftcms/server-check@72d6748 and the people I work with wondered why (they hope we don't need to switch the DB system)

[brandonkelly]

Maybe “deprecate” was the wrong word. We’re generally going to recommend MySQL over Maria, but still plan to support Maria for the forseeable future.

[Anubarak]

Thank you for your fast reply.

I really appreciate all your work and effort with Craft CMS. You did a great job 👍
Craft 5 feels so much better without unnecessary Elements. At first I was a bit scared of your JSON solution but it seems to pay off with indexed JSON functionality from PostgreSQL and MySQL

[brandonkelly]

Glad you’re liking v5 Robin :)

[westhouseit]

Interesting, I didn't even realize this would be an issue. Any chance you could update the notice in the docs to a post that explains the issues?

It sounds to me like you're headed down a mixed RDBMS+NoSQL path. Still that should be better than tables with hundreds of columns.

[MattWilcox]

Given this (somewhat surprising) info, what are the steps to take an existing project that's in Maria (because that's been the default "MySQL" in most distro's for many years now) and port it to Oracle's MySQL?

I'm kinda surprised MySQL is still being considered the "main database" to develop against, and not Maria, tbh. Given the overall ecosystem.

Are we able to Craft database export from a Maria install, remove Maria from the server, install "real MySQL", and safely import?

[Anubarak]

Yeah but in order to use those indexed columns you have to write custom SQL as there is no good way to target those inside Field::getValueSql unless you want to overwrite either all fields or all ElementQueries as those functions are private.

So in case performance matters you have to switch the DB system. I often had to add indexes on the fly as my applications grew over the years but adding an index won't do it in that case. For MariaDB you have to change all the queries as well.
Still using MariaDB will be a no-go in that case.

[brandonkelly]

Are we able to Craft database export from a Maria install, remove Maria from the server, install "real MySQL", and safely import?

Yep, that should work fine.

[brandonkelly]

I'm kinda surprised MySQL is still being considered the "main database" to develop against, and not Maria, tbh. Given the overall ecosystem.

Agree; it was Maria’s game to lose, but they lost anyway. Internal dysfunction, increasingly poor performance, bugs going unfixed, and not keeping up with other major players on critical features like functional indexes…

[MattWilcox]

Yep, fair enough, makes sense - just goes to show how much changes if you're not keeping tabs on things too. I was also really surprised to see the internal fighting at MariaDB from last year I hadn't known about previously. Time for me to look at replacing MariaDB on our newest Debian server then. Thanks!

[westhouseit]

Doesn't have to be MySQL, there's also Postgres. ;-)