How to make filtering and sorting JSON data in MySQL faster?

Question

I have table "listing_data" which is about 150.000 rows. I have problem with filtering and sorting when the data comes from JSON. I have added keys on each column which needed for filtering and sorting.

However, as one column "data" used to store JSON use "TEXT" so I cannot add index there. I am trying to run this query

SELECT 
  id, 
  title, 
  slug, 
  description, 
  uniqueId, 
  data, 
  images, 
  view, 
  contact, 
  lastSync, 
  status, 
  sold, 
  waktu, 
  FLOOR(
    JSON_UNQUOTE(
      JSON_EXTRACT(data, '$.price')
    )/ 100000000
  )* 100 AS rangePrice, 
  COUNT(id) AS total 
FROM 
  listing_data 
WHERE 
  JSON_UNQUOTE(
    JSON_EXTRACT(data, '$.subCategory')
  )= 'rumah' 
  and JSON_UNQUOTE(
    JSON_EXTRACT(data, '$.type')
  )= 'dijual' 
  and LOWER(
    JSON_UNQUOTE(
      JSON_EXTRACT(data, '$.province')
    )
  )= 'lampung' 
  and LOWER(
    JSON_UNQUOTE(
      JSON_EXTRACT(data, '$.regency')
    )
  )= 'bandar lampung' 
  and LOWER(
    JSON_UNQUOTE(
      JSON_EXTRACT(data, '$.district')
    )
  )= 'way halim' 
GROUP BY 
  JSON_UNQUOTE(
    JSON_EXTRACT(data, '$.price')
  ) 
ORDER BY 
  rangePrice ASC 
LIMIT 
  0, 20

It took 2.8148 seconds to run. Is there any solution how to make it faster? I want to know if there is a way to add index into TEXT column.

In MySQL 5.7 and higher you can make indexes on generated columns. But the index must be for those specific columns. It wouldn't be suitable if the JSON fields you search for are changing all the time, and I am guessing they will be. — Bill Karwin
– Bill Karwin, Commented Mar 27, 2021 at 7:38

KIKO Software · Accepted Answer · 2021-03-27 07:36:09Z

2

You could extract all needed data from the JSON column and put it in new columns.

These new columns can be of types that can be indexed.

Perhaps you can even get rid of the JSON column completely?

answered Mar 27, 2021 at 7:36

KIKO Software

17.5k3 gold badges33 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rick James · Accepted Answer · 2021-03-27 17:50:44Z

1

LOWER gets in the way of optimization. Use a suitable collation.
JSON_UNQUOTE gets in the way of optimization; why do you need it?
Pull out commonly used columns (especially price or rangeprice, and general geographic area, probably also type) into their own columns.
Then build selected 'composite' indexes on them
The GROUP BY is invalid since it does not include various other columns. Perhaps that needs to be a separate query or a second pass over the data.
Use COUNT(*) instead of COUNT(id); the latter requires unnecessarily testing id IS NOT NULL.

answered Mar 27, 2021 at 17:50

Rick James

144k15 gold badges144 silver badges255 bronze badges

Collectives™ on Stack Overflow

How to make filtering and sorting JSON data in MySQL faster?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related