2

I have a query that is very fast in finding the records it wants, but it takes too long to "extract" the data from the JSON field if I have a few hundred results. Is there a way to speed up the extraction process with this query and data structure? At about 500 results, the query takes around 3 seconds. If I remove the extract, it's basically instant.

The query returns exactly the data that I want, it's just too slow:

        SELECT
            unit_uid,
            JSON_EXTRACT(rateplans, '$."2023-03-14"','$."2023-03-15"') AS rateplans
        FROM
            property_listings_rateplans
        WHERE
            unit_uid IN (1527,1639,5,9,17,20,24,27,1873,4279)

The structure of the data is like this but with a lot more values and more dates in the rateplans data:

unit_uid (int) rateplans (JSON)
1527 { "2023-03-14": { "date": "2022-12-02", "price": 17500, "online": 1 }, "2023-03-15": { "date": "2022-12-03", "price": 17500, "online": 1 }, }
1639 { "2023-03-14": { "date": "2022-12-02", "price": 17500, "online": 1, }, "2023-03-15": { "date": "2022-12-03", "price": 17500, "online": 1, },}

Any ideas on how I should tackle this?

Edit: I should also add that there will eventually be millions of unit_uids and approximately 2 years worth of dates in each JSON field as well as more key:value pairs within each date. This is just a sample of the live data.

Improve this question
17
  • 4
    I would probably just remove the JSON dependency completely by separating each of the json values into it's own record. Commented Jan 24, 2023 at 17:05
  • 3
    Yes, design a proper relational database Commented Jan 24, 2023 at 17:05
  • That isn't possible. The dataset I'm displaying here is much smaller than the live data. There are ~2 years worth of dates in the JSON as well as more fields within each date. Commented Jan 24, 2023 at 17:47
  • Is this real MySQL or is it only MariaDB? Run SELECT VERSION(); and report the result. Commented Jan 24, 2023 at 18:06
  • Second question: Is the column defined as the JSON data type, or did you define it as TEXT and simply store JSON content in the TEXT column? Commented Jan 24, 2023 at 18:07
Related questions
1
Merging table css
6
HTML Table with No Space
0
Create two tables with space between
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.