Make like operator with multiple ORs faster?

Databases
1

I am using like to find separate words in table that stores json data:

Json:

{"address_components":[{"long_name":"Calle Puerto Rico","short_name":"Calle Puerto Rico","types":["route"]},{"long_name":"Huelva","short_name":"Huelva","types":["locality","political"]},{"long_name":"Huelva","short_name":"Huelva","types":["administrative_area_level_2","political"]},{"long_name":"Andalucía","short_name":"AL","types":["administrative_area_level_1","political"]},{"long_name":"España","short_name":"ES","types":["country","political"]},{"long_name":"21005","short_name":"21005","types":["postal_code"]}],"formatted_address":"Calle Puerto Rico, 21005 Huelva, España","geometry":{"bounds":{"south":37.2829639,"west":-6.943094999999971,"north":37.2835676,"east":-6.9426796000000195},"location":{"lat":37.28329129999999,"lng":-6.943091900000013},"location_type":"GEOMETRIC_CENTER","viewport":{"south":37.2819167697085,"west":-6.944236280291534,"north":37.2846147302915,"east":-6.941538319708457}},"place_id":"ChIJY-R7zcHPEQ0RKhJrb9j_Ec8","types":["route"]}

AND LIKE:

where json LIKE '%\"espana\"%' OR json LIKE '% espana %' OR json LIKE '% espana\"%' OR json LIKE '%\"espana %' OR json like '%espana\"%' OR json like '%\"espana,%' OR json LIKE '% espana,%'

The problem is this is slow as hell and it causes a Ripple Effect on the rest of the site making it load slow and crash.

How can I match individual words in json faster?

Thanks

PS. My server is MYSQL

2

You didn’t show the full query, and I don’t know if all those redundant españas are example substitutions of what are really different words, (if they are “real” you don’t need them all).

Anyway, if you do an EXPLAIN SELECT ... the rest of the query followed by a SHOW WARNINGS; you should be able to spot areas for improvement and see how the optimizer is running the query.

3

Thanks buddy for security reason I can’t show whole query.

My sql is 5.6 and I am looking for a way to parse json and then look for a match?

Any ideas please?

4

This may be useful. The script searches title and memo fields in a Mysql table which has about 3,500 records:

5

if those individual words were pre-parsed into a table where each word is a single value in a column

aha… that’s an issue, then

because that sql is always (always) going to use a table scan

doing an EXPLAIN should confirm this

increased memory/hardware is all i can come up with

6

Appreciate guys. I am on a shared hosting :slight_smile: don’t have that luxury

7

The link to the demo is running on a $5.00/month server with 1Gb ram and a 20 Gig SSD

8

Because you’re not at liberty to show much of anything “real”, it will be up to you to do the footwork. But I think you may be able to get some performance improvement by using JSON_EXTRACT in a derived table subquery and SELECTing from that eg.

 CREATE TABLE `json_row` (
  `id` tinyint(1) NOT NULL AUTO_INCREMENT,
  `json` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `json_row` (`json`)
  VALUES ('{"address_components":[{"long_name":"Calle Puerto Rico","short_name":"Calle Puerto Rico","types":["route"]},{"long_name":"Huelva","short_name":"Huelva","types":["locality","political"]},{"long_name":"Huelva","short_name":"Huelva","types":["administrative_area_level_2","political"]},{"long_name":"Andalucía","short_name":"AL","types":["administrative_area_level_1","political"]},{"long_name":"España","short_name":"ES","types":["country","political"]},{"long_name":"21005","short_name":"21005","types":["postal_code"]}],"formatted_address":"Calle Puerto Rico, 21005 Huelva, España","geometry":{"bounds":{"south":37.2829639,"west":-6.943094999999971,"north":37.2835676,"east":-6.9426796000000195},"location":{"lat":37.28329129999999,"lng":-6.943091900000013},"location_type":"GEOMETRIC_CENTER","viewport":{"south":37.2819167697085,"west":-6.944236280291534,"north":37.2846147302915,"east":-6.941538319708457}},"place_id":"ChIJY-R7zcHPEQ0RKhJrb9j_Ec8","types":["route"]}');

SELECT `id`, `json` FROM `json_row` WHERE `json` LIKE '%Puerto%';

SELECT `id`, JSON_EXTRACT(`json`, '$.formatted_address') FROM `json_row`
WHERE JSON_EXTRACT(`json`, '$.address_components[0].long_name') LIKE '%Puerto%';
9

You have two options.

  1. Move content out of JSON and into columns.

  2. Change the type of the column to the new JSON datatype (Require MySQL 5.7)
    Then use the built in functionality to search the JSON content quicker as @Mittineague mention, though dont use that example if you go this route, instead review the documentation I linked to below.

https://dev.mysql.com/doc/refman/5.7/en/json.html
https://dev.mysql.com/doc/refman/5.7/en/json-functions.html

10

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.