-
Problem report
-
Resolution: Won't fix
-
Trivial
-
3.4.14, 4.0.0rc1
-
Sprint 56 (Sep 2019), Sprint 55 (Aug 2019), Sprint 54 (Jul 2019), Sprint 57 (Oct 2019), Sprint 58 (Nov 2019)
-
1
It is possible to add such index:
create index problem_4 on problem (`r_eventid`,`source`,`object`,`objectid`);
Instead of
KEY `problem_1` (`source`,`object`,`objectid`), KEY `problem_3` (`r_eventid`),
Which in some conditions results in query speed up:
| 69 | 0.00072350 | select eventid,objectid from problem where source=0 and object=0 and objectid=15848 and r_eventid is null | +----------------------+----------+ | Status | Duration | +----------------------+----------+ | starting | 0.000121 | | checking permissions | 0.000015 | | Opening tables | 0.000031 | | init | 0.000044 | | System lock | 0.000016 | | optimizing | 0.000029 | | statistics | 0.000256 | | preparing | 0.000030 | | executing | 0.000009 | | Sending data | 0.000068 | | end | 0.000011 | | query end | 0.000015 | | closing tables | 0.000014 | | freeing items | 0.000037 | | cleaning up | 0.000030 | +----------------------+----------+ 15 rows in set, 1 warning (0.00 sec)
explain select eventid,objectid from problem where source=0 and object=0 and objectid=15848 and r_eventid is null; +----+-------------+---------+------------+------+-------------------------------+-----------+---------+-------------------------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+-------------------------------+-----------+---------+-------------------------+------+----------+--------------------------+ | 1 | SIMPLE | problem | NULL | ref | problem_1,problem_3,problem_4 | problem_4 | 25 | const,const,const,const | 1 | 100.00 | Using where; Using index | +----+-------------+---------+------------+------+-------------------------------+-----------+---------+-------------------------+------+----------+--------------------------+
Without index:
| 74 | 0.56967675 | select eventid,objectid from problem where source=0 and object=0 and objectid=15848 and r_eventid is null | +----------------------+----------+ | Status | Duration | +----------------------+----------+ | starting | 0.000052 | | checking permissions | 0.000006 | | Opening tables | 0.000011 | | init | 0.000016 | | System lock | 0.000005 | | optimizing | 0.000009 | | statistics | 0.000077 | | preparing | 0.000010 | | executing | 0.000002 | | Sending data | 0.569437 | | end | 0.000009 | | query end | 0.000014 | | closing tables | 0.000005 | | freeing items | 0.000015 | | cleaning up | 0.000011 | +----------------------+----------+
explain select eventid,objectid from problem where source=0 and object=0 and objectid=15848 and r_eventid is null; +----+-------------+---------+------------+-------------+---------------------+---------------------+---------+------+--------+----------+----------------------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------------+---------------------+---------------------+---------+------+--------+----------+----------------------------------------------------------------+ | 1 | SIMPLE | problem | NULL | index_merge | problem_1,problem_3 | problem_1,problem_3 | 16,9 | NULL | 711864 | 100.00 | Using intersect(problem_1,problem_3); Using where; Using index | +----+-------------+---------+------------+-------------+---------------------+---------------------+---------+------+--------+----------+----------------------------------------------------------------+
Some info about database:
mysql> select count(*) from problem where objectid=15848 and r_eventid is null; +----------+ | count(*) | +----------+ | 1 | +----------+ 1 row in set (1.61 sec) mysql> select count(*) from problem where objectid=15848 and r_eventid is not null; +----------+ | count(*) | +----------+ | 2000004 | +----------+ 1 row in set (2.66 sec) mysql> select count(*) from problem where objectid<>15848 and r_eventid is null; +----------+ | count(*) | +----------+ | 1000010 | +----------+ 1 row in set (1.59 sec) mysql> select count(*) from problem where objectid<>15848 and r_eventid is not null; +----------+ | count(*) | +----------+ | 9 | +----------+ 1 row in set (0.48 sec)
As you see r_eventid index cannot be used efficiently in case there are lots of not recovered problems that do not belong to trigger that query is searching for.
Though this is probably minor issue if problem table is not too big.
