Massive Performance Gains on JSON Column Queries using MySql Virtual Columns and Indexes in Laravel

If your application performs queries against a Mysql JSON column, you may have come across the scenario where the query became a performance bottleneck, slowing down your application. Normally you’d use an index, but Mysql does not allow creating indexes involving JSON columns. So what else is there to try?

Luckily, Mysql 5.7 introduced “Generated Columns”, which are derived columns that can be indexed. In this article I’d like to share the approach my team used, and an example of how with minimal effort we realized massive performance gains in a PHP application running on Laravel.

I’m hoping this article will help someone have a similar experience.

In a nutshell, Generated Columns:

• Can be Virtual or Stored.
• Their values are computed from an expression supplied at the time the column is created. In the context of Laravel, you’d most likely do this in a migration (example code below).
• They can be queried just like regular SQL columns.
• Virtual Columns are evaluated when rows are read, (immediately after any BEFORE triggers) and do not take up any storage.
• Stored Columns require storage space, and are evaluated when rows are inserted or updated.

My recommendation would be to start with Virtual columns in your solutions. Only use Stored columns if you absolutely need to for your use case, or if Virtual columns become a performance bottleneck.

My Team’s Experience (aka “The Problem“)

After onboarding a new client and setting up their customized workflow (that’s where JSON columns come in handy for us), we noticed that one endpoint had become extremely slow, and the bottleneck was the Mysql query. Shortly after deployment we noticed that this had become our most time consuming query. Thankfully this was in a background job, thus the User Experience was not affected. However, 3.2s for this query was still unacceptable.

Our Solution

To optimize the slow query, we created virtual columns, so we could index them to speed up the query.

Steps

• First, create a new migration.

php artisan make:migration YourMigrationName --table=your_table_name

• Define Virtual Columns using “virtualAs()”. See a few examples below of creating virtual fields from a JSON column.

public function up()
{
    Schema::table('users', function (Blueprint $table) {
        $table->string('last_name')->virtualAs('JSON_UNQUOTE(json->"$.fetched_profile.name.family_last_name")')->index();
        $table->unsignedInteger('external_id')->virtualAs('JSON_UNQUOTE(json->"$.api.user_id")')->index();
        
        // For a Stored Column use storedAs instead of virtualAs
        // $table->string('last_name')->storedAs('JSON_UNQUOTE(json->"$.fetched_profile.name.family_last_name")')->index();
    });
}

• You may fill the down method as you’d regularly do

public function down()
{
    Schema::table('users', function (Blueprint $table) {
        $table->dropColumn('last_name');
        $table->dropColumn('external_id');
    });
}

After successfully running php artisan migrate you should be able to query your Virtual fields as you normally would.

For example User::where('external_id', $id)->first();

Our Results

In the specific case I described above, the fairly straightforward changes above resulted in the query going from being the slowest taking 3.2 seconds on average, to taking 2.35 ms on average. Quite the difference, ey?

In the first 20 minutes after deployment, the average came down to 0.0158 seconds. For context, the screenshot was taken viewing last 30 minutes in our New Relic dashboard, so the average was still affected by the previous metrics.

This is a screenshot I just took of the past 3 days, This is not appearing on the top transactions anymore, that’s why I can’t get a similar screenshot as the above.

It’s worth noting the migration was performed on a table containing millions of rows, occupying over 62GB of data.

Have you ever used Generated Columns (Virtual or Stored) for performance optimizations in this context? If so, I’d love to hear your experience.