0

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%';

  1. By using like wildcard (case A)

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

  1. 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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.