When editing CDEF's, slow database performance can occur

[eschoeller]

When opening up Presets -> CDEF I'm also seeing very long query times:

# Time: 191024 20:26:41
# User@Host: apache[apache] @ localhost []  Id: 288635648
# Query_time: 5.188121  Lock_time: 0.000384 Rows_sent: 1  Rows_examined: 292
SET timestamp=1571970401;
SELECT COUNT(`rows`) FROM ( SELECT cd.id AS `rows`, SUM(CASE WHEN local_graph_id>0 THEN 1 ELSE 0 END) AS graphs FROM cdef AS cd LEFT JOIN graph_templates_item AS gti ON gti.cdef_id=cd.id WHERE `system`=0 GROUP BY cd.id ) AS rs;

# Time: 191024 20:26:48
# User@Host: apache[apache] @ localhost []  Id: 288635648
# Query_time: 6.595178  Lock_time: 0.000324 Rows_sent: 50  Rows_examined: 52821
SET timestamp=1571970408;
SELECT rs.*, SUM(CASE WHEN local_graph_id=0 THEN 1 ELSE 0 END) AS templates, SUM(CASE WHEN local_graph_id>0 THEN 1 ELSE 0 END) AS graphs FROM ( SELECT cd.*, gti.local_graph_id FROM cdef AS cd LEFT JOIN graph_templates_item AS gti ON gti.cdef_id=cd.id WHERE `system`=0 GROUP BY cd.id, gti.graph_template_id, gti.local_graph_id ) AS rs WHERE `system`=0 GROUP BY rs.id ORDER BY `name` ASC LIMIT 0,50;

[netniV]

Have you tried running that manually? Do you see the same slowness?

If so, have you tried running the inner queries to see if you can identify which part is slow?

You can also use EXPLAIN to see what elements might be slowing your queries down:

EXPLAIN SELECT COUNT(`rows`) FROM ( SELECT cd.id AS `rows`, SUM(CASE WHEN local_graph_id>0 THEN 1 ELSE 0 END) AS graphs FROM cdef AS cd LEFT JOIN graph_templates_item AS gti ON gti.cdef_id=cd.id WHERE `system`=0 GROUP BY cd.id ) AS rs;

and

EXPLAIN SELECT rs.*, SUM(CASE WHEN local_graph_id=0 THEN 1 ELSE 0 END) AS templates, SUM(CASE WHEN local_graph_id>0 THEN 1 ELSE 0 END) AS graphs FROM ( SELECT cd.*, gti.local_graph_id FROM cdef AS cd LEFT JOIN graph_templates_item AS gti ON gti.cdef_id=cd.id WHERE `system`=0 GROUP BY cd.id, gti.graph_template_id, gti.local_graph_id ) AS rs WHERE `system`=0 GROUP BY rs.id ORDER BY `name` ASC LIMIT 0,50;

https://dzone.com/articles/understanding-mysql-queries-with-explain

[xmacan]

I have the same problem though I have less rows (examined rows are 282 and 16000).

Query time is 1-3 seconds manually run has the same time:
MariaDB [cacti]> explain SELECT cd.id AS `rows`,SUM(CASE WHEN local_graph_id>0 THEN 1 ELSE 0 END) AS graphs FROM cdef AS cd LEFT JOIN graph_templates_item AS gti ON gti.cdef_id=cd.id WHERE `system`=0 GROUP BY cd.id;
+------+-------------+-------+------+---------------+------+---------+------+-------+-------------------------------------------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra                                           |
+------+-------------+-------+------+---------------+------+---------+------+-------+-------------------------------------------------+
|    1 | SIMPLE      | cd    | ALL  | NULL          | NULL | NULL    | NULL |    71 | Using where; Using temporary; Using filesort    |
|    1 | SIMPLE      | gti   | ALL  | NULL          | NULL | NULL    | NULL | 82987 | Using where; Using join buffer (flat, BNL join) |
+------+-------------+-------+------+---------------+------+---------+------+-------+-------------------------------------------------+
2 rows in set (0.00 sec)

My DB isn't on SSD disks.

[cigamit]

Using @eschoeller data, before 6.2 seconds, after 0.257 seconds.

[eschoeller]

Much faster!
Before:

# Time: 191110 18:23:42
# User@Host: apache[apache] @ localhost []  Id: 187444401
# Query_time: 5.910617  Lock_time: 0.016709 Rows_sent: 1  Rows_examined: 292
SET timestamp=1573435422;
SELECT COUNT(`rows`) FROM ( SELECT cd.id AS `rows`, SUM(CASE WHEN local_graph_id>0 THEN 1 ELSE 0 END) AS graphs FROM cdef AS cd LEFT JOIN graph_templates_item AS gti ON gti.cdef_id=cd.id WHERE `system`=0 GROUP BY cd.id ) AS rs;                                                                                                                                                                                                                                 
# Time: 191110 18:23:49                                                                                                                                                                                                           
# User@Host: apache[apache] @ localhost []  Id: 187444401                                                                                                                                                                         
# Query_time: 6.888013  Lock_time: 0.000284 Rows_sent: 50  Rows_examined: 52959                                                                                                                                                   
SET timestamp=1573435429;                                                                                                                                                                                                         
SELECT rs.*, SUM(CASE WHEN local_graph_id=0 THEN 1 ELSE 0 END) AS templates, SUM(CASE WHEN local_graph_id>0 THEN 1 ELSE 0 END) AS graphs FROM ( SELECT cd.*, gti.local_graph_id FROM cdef AS cd LEFT JOIN graph_templates_item AS gti ON gti.cdef_id=cd.id WHERE `system`=0 GROUP BY cd.id, gti.graph_template_id, gti.local_graph_id ) AS rs WHERE `system`=0 GROUP BY rs.id ORDER BY `name` ASC LIMIT 0,50;

After:

# Time: 191110 18:26:02
# User@Host: apache[apache] @ localhost []  Id: 187408472
# Query_time: 1.294510  Lock_time: 0.000428 Rows_sent: 1  Rows_examined: 444583
SET timestamp=1573435562;
SELECT COUNT(`rows`) FROM ( SELECT cd.id AS `rows`, SUM(CASE WHEN local_graph_id>0 THEN 1 ELSE 0 END) AS graphs FROM cdef AS cd LEFT JOIN ( SELECT DISTINCT cdef_id, local_graph_id, graph_template_id FROM graph_templates_item ) AS gti ON gti.cdef_id=cd.id WHERE `system`=0 GROUP BY cd.id ) AS rs;

[cigamit]

Cool!