Mysql – Is the MySQL JSON data type bad for performance for data retrieval

jsonMySQLmysql-5.7performance

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.

Best Answer

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.