CODEWITHSUNDEEP
mysqljsonindexingfull-text-search
user4942382
user4942382

Reputation:

MySql Index for JSON

I have this SQL Query:

SELECT  players.*,
        CAST(
            SUBSTRING_INDEX(
                SUBSTRING_INDEX(achievements, '"globalID":23000062', 1),
                '"value":',
                -1
            )
        AS UNSIGNED) AS json_extracted_value
FROM    players
WHERE   INSTR(achievements, '"globalID":23000062') > 0

What would be the most reasonable Index for the achievements column? Would it be a normal index or a fulltext index? Right now the query takes around 1 minute to complete. I would like it completing within a few seconds.

Also is there another easier query I could use? If not, then what index would be best?

Thanks!

Upvotes: 1

Views: 546

Answers (2)

Rick James
Rick James

Reputation: 142560

Upgrade...

MySQL 5.7 can index JSON column fields.

MariaDB 10's "Dynamic columns" have similar features.

Upvotes: 1

itoctopus
itoctopus

Reputation: 4271

You should create another field in your table (or in an alternative table) that will contain the json data that you want to index on (for example, the globalID field).

Doing it in any other way will probably not work (unless there is a new way to index json data that I don't know of).

We have encountered this same issue many times and we were only able to solve it by creating an additional field containing the json field that we want to search for.

Upvotes: 0

Related Questions