Ongoing Performance Issues with MySQL

[larsborn]

Our performance issues are almost certainly caused by this query:

(SELECT id FROM tbl_sample_sources WHERE source LIKE '%$queryValue%' LIMIT 100)
UNION
(SELECT id from tbl_samples WHERE JSON_SEARCH( lower(yara->'$.yara'), 'all', '$queryValue') IS NOT NULL LIMIT 100)

Both UNION-parts of it are not supported by an index right now. This leads to two full table scans of the (very large) tbl_samples table for every search request.

1. I suggest we change that for the first half by removing the %. This will effectively decrease our feature set there (we only support prefix queries) but I think the performance benefit is worth it. Down the road, we could build a full-text search on the source field to add that feature back in.
2. the second half of the UNION is probably harder to fix: indexing fields in JSON seems to be supported in MySQL by creating a virtual column and then putting an index on that. Our situation here is a bit more complex though, the yara field in the JSON in the yara [sic] column is a list. I'll research if that's even supported. If not, I suggest we properly de-normalize: have a tbl_yara table containing all YARA rule names and have a tbl_matches table relating entries in that table to rows in the tbl_sample table. For this, we need to touch the YARA scanning process and build a tool to migrate existing data.

[larsborn]

Created two new tables:

CREATE TABLE `tbl_yara` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `rule_name` varchar(255) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `rule_name_UNIQUE` (`rule_name`)
) ENGINE=InnoDB AUTO_INCREMENT=1135 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `tbl_matches` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `yara_id` int(11) NOT NULL,
  `sample_id` int(10) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `yara_id_sample_id_unique_index` (`yara_id`,`sample_id`),
  KEY `yara_id_idx` (`yara_id`),
  KEY `sample_id_idx` (`sample_id`),
  CONSTRAINT `yara_id_FK` FOREIGN KEY (`yara_id`) REFERENCES `tbl_yara` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

and currently migrating the data. Script is idempotent, so when it's through, we can switch the YARA matcher over and just re-run it a second time.

[silascutler]

Is this good to close out?

[larsborn]

I forgot to update this issue: the data is now inserted into these two new tables and the Frontend is using them. The corresponding searches without an index are gone.

There's one thing left though: when I execute the above idempotent script to migrate the data, it'll still insert something, which is surprising. May there be other processes inserting data into the yara field of the sample table?