Is the MySQL JSON data type bad for performance for data retrieval? Let's say I have a MySQL JSON data type called custom_properties for a media table:



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-&gt;company_id', Auth::user()-&gt;company_id)-&gt;orderBy('created_at', 'DESC')-&gt;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.
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.