1

I have this query:

SELECT COUNT(*) AS "Other"
FROM events
JOIN triggers ON events.objectid = triggers.triggerid
WHERE events.name REGEXP "No data from SAM0056(.FTG|.REC|.LEFT|.RIGHT|.BACK), .* offline"
AND events.value = 1 
AND triggers.value = 1;

In total I need to execute 32 variants of these query depending on the REGEXP. The part "SAM0056" ranges form "SAM50" to "SAM0082". So the pattern is "No data from SAM00XX(.FTG|.REC|.LEFT|.RIGHT|.BACK), .* offline" where XX ranges from 50 to 82.

I also need to execute these query every 15 seconds. So I have a lot of select statements.

These are the DDL for events and triggers:

CREATE TABLE `events` (
  `eventid` bigint(20) unsigned NOT NULL,
  `source` int(11) NOT NULL DEFAULT '0',
  `object` int(11) NOT NULL DEFAULT '0',
  `objectid` bigint(20) unsigned NOT NULL DEFAULT '0',
  `clock` int(11) NOT NULL DEFAULT '0',
  `value` int(11) NOT NULL DEFAULT '0',
  `acknowledged` int(11) NOT NULL DEFAULT '0',
  `ns` int(11) NOT NULL DEFAULT '0',
  `name` varchar(2048) COLLATE utf8_bin NOT NULL DEFAULT '',
  `severity` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`eventid`),
  KEY `events_1` (`source`,`object`,`objectid`,`clock`),
  KEY `events_2` (`source`,`object`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

and

CREATE TABLE `triggers` (
  `triggerid` bigint(20) unsigned NOT NULL,
  `expression` varchar(2048) COLLATE utf8_bin NOT NULL DEFAULT '',
  `description` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
  `url` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
  `status` int(11) NOT NULL DEFAULT '0',
  `value` int(11) NOT NULL DEFAULT '0',
  `priority` int(11) NOT NULL DEFAULT '0',
  `lastchange` int(11) NOT NULL DEFAULT '0',
  `comments` text COLLATE utf8_bin NOT NULL,
  `error` varchar(2048) COLLATE utf8_bin NOT NULL DEFAULT '',
  `templateid` bigint(20) unsigned DEFAULT NULL,
  `type` int(11) NOT NULL DEFAULT '0',
  `state` int(11) NOT NULL DEFAULT '0',
  `flags` int(11) NOT NULL DEFAULT '0',
  `recovery_mode` int(11) NOT NULL DEFAULT '0',
  `recovery_expression` varchar(2048) COLLATE utf8_bin NOT NULL DEFAULT '',
  `correlation_mode` int(11) NOT NULL DEFAULT '0',
  `correlation_tag` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
  `manual_close` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`triggerid`),
  KEY `triggers_1` (`status`),
  KEY `triggers_2` (`value`,`lastchange`),
  KEY `triggers_3` (`templateid`),
  CONSTRAINT `c_triggers_1` FOREIGN KEY (`templateid`) REFERENCES `triggers` (`triggerid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

An explain-statement of the query shows this:

+------+-------------+----------+--------+--------------------+---------+---------+------------------------+------+-------------+
| id   | select_type | table    | type   | possible_keys      | key     | key_len | ref                    | rows | Extra       |
+------+-------------+----------+--------+--------------------+---------+---------+------------------------+------+-------------+
|    1 | SIMPLE      | events   | ALL    | NULL               | NULL    | NULL    | NULL                   | 8397 | Using where |
|    1 | SIMPLE      | triggers | eq_ref | PRIMARY,triggers_2 | PRIMARY | 8       | zabbix.events.objectid |    1 | Using where |
+------+-------------+----------+--------+--------------------+---------+---------+------------------------+------+-------------+

And an insert-statement into these 2 tables (events and triggers):

insert into events (eventid,source,object,objectid,clock,ns,value,name,severity) 
values (4985807,0,0,22281,1585805538,856070799,1,'No data from SAM0106-FTG, FortiGate offline',3)

INSERT INTO triggers (description,expression,recovery_mode,type,url,priority,comments,manual_close,status,correlation_mode,recovery_expression,correlation_tag,templateid,triggerid) 
VALUES ('CPU-Temperature to high','{29157}>35','0','0','','3','','1','0','0','','','22515','22541')

So how can I optimize these query? I use mariadb version 10.1.44 on a Ubuntu version 18.04.4.

12
  • 3
    Show DDL of the tables (like CREATE TABLE scripts) and an example of the data (as INSERT INTO scripts). 32 variants of these query depending on the REGEXP. Does some static and complete list of possible values in the events.name column exists? Or maybe there exists some pattern of these values?
    – Akina
    Commented Apr 1, 2020 at 10:52
  • You should make A EXPLAIN of your query and see if the indexes are correct. Of cours that is only additional to Akinas suggestion
    – nbk
    Commented Apr 1, 2020 at 14:41
  • Hello, @Akina, I have updated the question. Should I create an Index on the triggers.triggerId table? And makes it a difference if you join the table on the primary key or the index of the table?
    – Jarne
    Commented Apr 6, 2020 at 13:27
  • @jarne Your triggers table already has a PRIMARY key index on triggerid. No need for redundant indexes. Change SELECT COUNT(*) AS "Other" to SELECT COUNT(event_id) AS "Other" in your first query. What you do not have is an index for events.objectid (where objectid is the first column of the index), ADD the index, please and retest your first query with EXPLAIN to see the difference. Commented Apr 10, 2020 at 11:57
  • @jarne Are you aware MariaDB 10.1.44 became GA on 2020 01 28? Many people will not try a version less than 6 months old to avoid the 'find and report' bugs requirement for any version with no significant age in the wild. This is the URL for the extensive changelog - mariadb.com/kb/en/mariadb-10144-changelog Commented Apr 10, 2020 at 12:04

1 Answer 1

1

FULLTEXT(name) and MATCH(name) AGAINST('+SAM0106' IN BOOLEAN MODE)

That will let you quickly whittle down the list of possible rows. After that, the REGEXP (or whatever) can finish the filtering.

PRIMARY KEY(triggerid) is the other index that is useful for that SELECT.

Your Answer

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

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