Storing data as JSON vs meta table

Question

I am using MySQL 5.7.26 and have a very large table with multiple foreign keys for a column. I can store it with JSON type in a single table - tours (Case A) or by creating another table - like metas (Case B)

Case A countries and locations columns are JSON.

+----+-----------------+------------------------------------+-------+------+--------+
| id | countries       | locations                          | code  | type | status |
+----+-----------------+------------------------------------+-------+------+--------+
|  1 | ["3", "6", "2"] | ["6", "16", "4", "8", "7", "10"]   | ODxsh |    1 |      1 |
|  2 | ["6", "9", "3"] | ["2", "4", "14", "9", "16", "13"]  | pl44W |    2 |      1 |
|  3 | ["3", "7", "1"] | ["10", "12", "13", "3", "11", "5"] | B4e2Q |    2 |      1 |
+----+-----------------+------------------------------------+-------+------+--------+

Case B

Main table:

+----+-------+------+--------+
| id | code  | type | status |
+----+-------+------+--------+
|  1 | ODxsh |    1 |      1 |
|  2 | pl44W |    2 |      1 |
|  3 | B4e2Q |    2 |      1 |
+----+-------+------+--------+

Meta table: like meta key 1 reserved for a country, 2 for a location data.

+----+---------+---------+---------+--------+
| id | metakey | metaval | tour_id | status |
+----+---------+---------+---------+--------+
|  1 |       1 |       3 |       1 |      1 |
|  2 |       2 |       3 |       1 |      1 |
|  3 |       2 |       4 |       2 |      1 |
+----+---------+---------+---------+--------+

I must be able to filter out the main table like: SELECT * FROM tours WHERE countries column has value 6 AND locations column has value 5;

Now the problem is performance. I have 3 options:

1. By using JSON_CONTAINS (case A)

SELECT * FROM tours WHERE JSON_CONTAINS(tours.locations, '"5"') AND JSON_CONTAINS(tours.countries, '"6"') AND tours.code like '%v%';

2. By using like wildcard (case A)

SELECT * FROM tours WHERE tours.locations LIKE '%"5"%' AND tours.countries LIKE '%"6"%' AND tours.code LIKE '%v%'

3. By using row constructors (case B)

SELECT metas.tour_id AS tourID FROM metas INNER JOIN tours ON (tours.id = metas.tour_id) WHERE (metakey, metaval) IN ((1, 6), (2, 5)) AND tours.code like '%v%' GROUP BY metas.tour_id HAVING COUNT(metas.tour_id) = 2;

I added some test data and made some tests, according to the results the first option looks much more efficient. However, I am not really sure to store data with JSON. Should I use JSON or choose the option 3? Is it possible to use the index with JSON data? FYI, I have rare inserts and updates, lots of reads.

Answer 1

If I were you, I would use a classic normalised database structure rather than using JSON. I think that this is what you are driving at with your Option 3 (By using row constructors)?

I constructed a normalised version of your data. A sample is shown below - the full data is available at the sqlfiddle here.

CREATE TABLE country_location
(
  cl_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  country INTEGER NOT NULL,  -- you might wish to use TINYINT for some fields
  location INTEGER NOT NULL,
  cl_code CHAR (5) NOT NULL,
  cl_type INTEGER NOT NULL,
  cl_status INTEGER NOT NULL,
  CONSTRAINT country_location_code_uq UNIQUE (country, location, cl_code)
);

-- Experiment with these indexes on your own data! -- but the plans seem good - better than with no index.

CREATE INDEX cl_ix ON country_location (country, location);
-- CREATE INDEX country_ix ON country_location (country);
-- CREATE INDEX location_ix ON country_location (location);

And the data:

INSERT INTO country_location 
(country, location, cl_code, cl_type, cl_status) 
VALUES (3, 6, '0Dxsh', 1, 1);
INSERT INTO country_location 
(country, location, cl_code, cl_type, cl_status) 
VALUES (3, 16, '0Dxsh', 1, 1);
INSERT INTO country_location 
(country, location, cl_code, cl_type, cl_status) 
VALUES (3, 4, '0Dxsh', 1, 1);
INSERT INTO country_location 
(country, location, cl_code, cl_type, cl_status) 
VALUES (3, 8, '0Dxsh', 1, 1);

A sample SELECT of the data from this table:

cl_id   country location    cl_code cl_type cl_status
    1         3        6      0Dxsh       1         1
    2         3       16      0Dxsh       1         1
    3         3        4      0Dxsh       1         1
    4         3        8      0Dxsh       1         1

There's not enough data to be really sure, but with the indexes, the PLANs seem to be optimal and you would get excellent results on single country-location record searches.

I don't know enough about your data to do the joins - I've created the tables in the fiddle and I'm sure that with the proper FOREIGN KEYs, your system would be very performant.