Hi team,
I’ve been bothered by the duplicated slow query issue with loading my Gravity Flow inbox page in the frontend. Everytime I load this page, the MySQL CPU usage will spike drastically. Here’s the slow query in the slow log:
# Query_time: 8.089838 Lock_time: 0.000002 Rows_sent: 24 Rows_examined: 6193762
SET timestamp=1668565601;
SELECT SQL_CALC_FOUND_ROWS DISTINCT `t1`.`id` FROM `if_gf_entry` AS `t1` LEFT JOIN `if_gf_entry_meta` AS `m2` ON (`m2`.`entry_id` = `t1`.`id` AND `m2`.`meta_key` = 'workflow_user_id_172') LEFT JOIN `if_gf_entry_meta` AS `m3` ON (`m3`.`entry_id` = `t1`.`id` AND `m3`.`meta_key` = 'workflow_role_administrator') LEFT JOIN `if_gf_entry_meta` AS `m4` ON (`m4`.`entry_id` = `t1`.`id` AND `m4`.`meta_key` = 'workflow_role_order_administrator') WHERE (`t1`.`form_id` IN (1) AND (`t1`.`status` = 'active' AND ((`m2`.`meta_key` = 'workflow_user_id_172' AND `m2`.`meta_value` = 'pending') OR (`m3`.`meta_key` = 'workflow_role_administrator' AND `m3`.`meta_value` = 'pending') OR (`m4`.`meta_key` = 'workflow_role_order_administrator' AND `m4`.`meta_value` = 'pending')))) ORDER BY `t1`.`id` DESC LIMIT 999999;
As you can see the rows_examined is enormous, and the query_time is taking 8s, sometimes over 10-15s, the site admins open this page many times a day, such slow queries are unbearable for a production site. Is there a way to optimize this query and increase efficiency? Thanks a lot!