DEV Community

Yuka Kadokura
Yuka Kadokura

Posted on

3

To speed up the search process, let’s set an index for array data within a JSON-formatted column

This article introduces how to set an index for array data within JSON-formatted columns storing JSON data.

Firstly, setting an index on commonly queried columns is an effective way to speed up search processes.

However, storing JSON-formatted data in a database column and wanting to search specific array data within that JSON doesn’t typically speed up search processes even if you set an index, due to the usual specifications of indexing.

In such cases, when aiming to expedite the search process for array data within JSON-formatted columns, utilizing Multi-Valued Indexes would be beneficial.

By using Multi-Valued Indexes, it’s possible to set an index on array data stored in JSON format.

Let’s say the following data is stored in a JSON formatted ‘json_data_column’ column. As an assumption, let’s consider that the ‘json_data_column’ column contains JSON data in the same format. Furthermore, while it would be better to normalize the string data held in ‘skills->languages’ and ‘skills->frameworks’ with IDs, for now, let’s ignore that aspect.

{
    "id": 1,
    "skills": {
        "languages": [
            "PHP",
            "JavaScript",
            "Java"
        ],
        "frameworks": [
            "Laravel",
            "Vue.js",
            "Spring"
        ]
    }
}
Enter fullscreen mode Exit fullscreen mode

If you want to improve the search speed for the array data under the ‘skills->languages’ key within the ‘json_data_column’ column, you can set an index as follows.

ALTER TABLE json_data_column ADD INDEX members_skills_languages_index( (CAST(skills->’$.languages’ AS UNSIGNED ARRAY)) );

This enables faster search processing for the data under ‘json_data_column.skills->languages’ key. However, it’s important to note that, similar to a regular index, there are search operations where the index won’t take effect. While ‘exact match search’ and ‘prefix match search’ benefit from the index, operations like ‘partial match search’ or ‘suffix match search,’ such as the LIKE search, won’t benefit from the index and will result in a full scan.

Let’s set up the appropriate index, implement the suitable search process, and ensure it results in quick hits.

💡 One last tip before you go

DEV++

Spend less on your side projects

We have created a membership program that helps cap your costs so you can build and experiment for less. And we currently have early-bird pricing which makes it an even better value! 🐥

Check out DEV++

Top comments (0)

Some comments may only be visible to logged-in visitors. Sign in to view all comments.

👋 Kindness is contagious

Explore a sea of insights with this enlightening post, highly esteemed within the nurturing DEV Community. Coders of all stripes are invited to participate and contribute to our shared knowledge.

Expressing gratitude with a simple "thank you" can make a big impact. Leave your thanks in the comments!

On DEV, exchanging ideas smooths our way and strengthens our community bonds. Found this useful? A quick note of thanks to the author can mean a lot.

Okay