Let's say I have a MySQL JSON data type called custom_properties for a media table:

enter image description here

An example of the json data stored in the custom_properties column could be:

{
 "company_id": 1, 
 "uploaded_by": "Name", 
 "document_type": "Policy", 
 "policy_signed_date": "04/04/2018"
}

In my PHP Laravel app I would do something like this:

$media = Media::where('custom_properties->company_id', Auth::user()->company_id)->orderBy('created_at', 'DESC')->get();

This would fetch all media items belonging to company 1.

My question is that lets say we have 1 million media records, would this be a bad way to fetch records in terms of performance? Can anyone shed some light on how MySQL indexes JSON data types?

From the MySQL official docs:

JSON documents stored in JSON columns are converted to an internal format that permits quick read access to document elements. When the server later must read a JSON value stored in this binary format, the value need not be parsed from a text representation. The binary format is structured to enable the server to look up subobjects or nested values directly by key or array index without reading all values before or after them in the document.

  • Show us the SELECT and EXPLAIN SELECT and SHOW CREATE TABLE. – Rick James Apr 27 at 23:44

I think we can safely assume the performance would be worse than using regular columns.

The way to index JSON attributes is to create a virtual generated column for the particular attribute you want (company_id), then index the virtual column, and use that column to filter your query.

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.