Galera in MariaDB 10.2.18 (Ubuntu 16.04) DB-4 (WORKING FINE): MariaDB [databasename]> show table status like 'tasks'; MariaDB [databasename]> show table status like 'users'; MariaDB [databasename]> show table status like 'projecttasks'; MariaDB [databasename]> show table status like 'plugins'; +-------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +-------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+ | tasks | InnoDB | 10 | Dynamic | 452318 | 251 | 113901568 | 0 | 77840384 | 6291456 | NULL | 2018-10-23 01:23:56 | 2018-10-23 15:07:43 | NULL | utf8_general_ci | NULL | | | +-------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+ +-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+ | users | InnoDB | 10 | Dynamic | 431 | 228 | 98304 | 0 | 16384 | 0 | NULL | 2018-10-23 01:24:55 | 2018-10-23 01:24:56 | NULL | utf8mb4_general_ci | NULL | | | +-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+ +--------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +--------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+ | projecttasks | InnoDB | 10 | Dynamic | 528483 | 34 | 18366464 | 0 | 27295744 | 5242880 | NULL | 2018-10-23 01:23:44 | 2018-10-23 15:07:43 | NULL | utf8mb4_general_ci | NULL | | | +--------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+ +---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+ | plugins | InnoDB | 10 | Dynamic | 64 | 256 | 16384 | 0 | 32768 | 0 | NULL | 2018-10-23 01:23:44 | 2018-10-23 01:23:44 | NULL | utf8mb4_general_ci | NULL | | | +---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+ database maths, table noperation: +-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+ | noperation| InnoDB | 10 | Dynamic | 63 | 260 | 16384 | 0 | 16384 | 0 | NULL | 2018-07-18 11:50:13 | NULL | NULL | latin1_swedish_ci | NULL | | | +-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+ DB-5 (NOT WORKING FINE, SLOW RESULTS): MariaDB [databasename]> show table status like 'tasks'; MariaDB [databasename]> show table status like 'users'; MariaDB [databasename]> show table status like 'projecttasks'; MariaDB [databasename]> show table status like 'plugins'; +-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+ | tasks | InnoDB | 10 | Dynamic | 105 | 156 | 16384 | 0 | 65536 | 6291456 | NULL | 2018-10-23 01:23:56 | 2018-10-23 15:07:43 | NULL | utf8_general_ci | NULL | | | +-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+ +-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+ | users | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 16384 | 0 | NULL | 2018-10-23 01:24:56 | 2018-10-23 01:24:56 | NULL | utf8mb4_general_ci | NULL | | | +-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+ +--------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +--------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+ | projecttasks | InnoDB | 10 | Dynamic | 105 | 156 | 16384 | 0 | 32768 | 4194304 | NULL | 2018-10-23 01:23:45 | 2018-10-23 15:07:43 | NULL | utf8mb4_general_ci | NULL | | | +--------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+ +---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+ | plugins | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 32768 | 0 | NULL | 2018-10-23 01:23:44 | 2018-10-23 01:23:44 | NULL | utf8mb4_general_ci | NULL | | | +---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+ database maths, table noperation: +-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+ | noperation| InnoDB | 10 | Dynamic | 63 | 260 | 16384 | 0 | 16384 | 0 | NULL | 2018-10-22 17:00:53 | NULL | NULL | latin1_swedish_ci | NULL | | | +-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+ DB-6 (NOT WORKING FINE, SLOW RESULTS): MariaDB [databasename]> show table status like 'tasks'; MariaDB [databasename]> show table status like 'users'; MariaDB [databasename]> show table status like 'projecttasks'; MariaDB [databasename]> show table status like 'plugins'; +-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+ | tasks | InnoDB | 10 | Dynamic | 55 | 297 | 16384 | 0 | 65536 | 7340032 | NULL | 2018-10-23 01:23:56 | 2018-10-23 15:02:16 | NULL | utf8_general_ci | NULL | | | +-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+ +-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+ | users | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 16384 | 0 | NULL | 2018-10-23 01:24:56 | 2018-10-23 01:24:56 | NULL | utf8mb4_general_ci | NULL | | | +-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+ +--------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +--------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+ | projecttasks | InnoDB | 10 | Dynamic | 55 | 297 | 16384 | 0 | 32768 | 4194304 | NULL | 2018-10-23 01:23:45 | 2018-10-23 15:02:16 | NULL | utf8mb4_general_ci | NULL | | | +--------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+ +---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+ | plugins | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 32768 | 0 | NULL | 2018-10-23 01:23:44 | 2018-10-23 01:23:44 | NULL | utf8mb4_general_ci | NULL | | | +---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+ database maths, table noperation: +-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+ | noperation| InnoDB | 10 | Dynamic | 63 | 260 | 16384 | 0 | 16384 | 0 | NULL | 2018-10-22 14:35:45 | NULL | NULL | latin1_swedish_ci | NULL | | | +-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+ EXPLAIN PLANS: DB-4 (This is working) MariaDB [(none)]> explain SELECT COUNT(1) FROM (SELECT `t1`.`id`, `t1`.`plugin_id`, `t1`.`configuration`, `t1`.`hash`, `t1`.`run_until`, `t1`.`num_runs`, `t1`.`target_type`, `t1`.`interval`, `t1`.`created`, `t1`.`next_run`, `t1`.`latest_run`, `t1`.`status`, `t1`.`owner_id`, `t1`.`created_for_id`, `t1`.`sel`, `t1`.`filters`, `t1`.`tags` FROM `databasename`.`tasks` AS t1 INNER JOIN `databasename`.`users` AS t2 ON (`t1`.`owner_id` = `t2`.`id`) INNER JOIN `databasename`.`users` AS t3 ON (`t1`.`created_for_id` = `t3`.`id`) INNER JOIN `databasename`.`plugins` AS t4 ON (`t1`.`plugin_id` = `t4`.`id`) INNER JOIN `databasename`.`projecttasks` AS t5 ON (`t1`.`id` = `t5`.`task_id`) LEFT OUTER JOIN `maths`.`noperation` AS t6 ON (`t4`.`pluginname` = `t6`.`mathsname`) WHERE ((`t1`.`status` != 'pending') AND (`t1`.`sel` LIKE '1234567890')) GROUP BY `t1`.`id`) AS testme; +------+-------------+------------+--------+-----------------------------------------------------+----------------------+---------+--------------------------------+------+--------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------+--------+-----------------------------------------------------+----------------------+---------+--------------------------------+------+--------------------------------------------------------------+ | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 63 | | | 2 | DERIVED | t1 | range | PRIMARY,tasks_plugin_id,tasks_owner_id,sel_idx | sel_idx | 768 | NULL | 1 | Using where; Using temporary; Using filesort | | 2 | DERIVED | t4 | eq_ref | PRIMARY | PRIMARY | 4 | databasename.t1.plugin_id | 1 | | | 2 | DERIVED | t2 | eq_ref | PRIMARY | PRIMARY | 4 | databasename.t1.owner_id | 1 | Using index | | 2 | DERIVED | t3 | eq_ref | PRIMARY | PRIMARY | 4 | databasename.t1.created_for_id | 1 | Using index | | 2 | DERIVED | t5 | ref | projecttasks_task_id | projecttasks_task_id | 4 | databasename.t1.id | 1 | Using index | | 2 | DERIVED | t6 | index | NULL | opname | 259 | NULL | 63 | Using where; Using index; Using join buffer (flat, BNL join) | +------+-------------+------------+--------+-----------------------------------------------------+----------------------+---------+--------------------------------+------+--------------------------------------------------------------+ DB-5 (this is wrong/slow) MariaDB [databasename]> explain SELECT COUNT(1) FROM (SELECT `t1`.`id`, `t1`.`plugin_id`, `t1`.`configuration`, `t1`.`hash`, `t1`.`run_until`, `t1`.`num_runs`, `t1`.`target_type`, `t1`.`interval`, `t1`.`created`, `t1`.`next_run`, `t1`.`latest_run`, `t1`.`status`, `t1`.`owner_id`, `t1`.`created_for_id`, `t1`.`sel`, `t1`.`filters`, `t1`.`tags` FROM `databasename`.`tasks` AS t1 INNER JOIN `databasename`.`users` AS t2 ON (`t1`.`owner_id` = `t2`.`id`) INNER JOIN `databasename`.`users` AS t3 ON (`t1`.`created_for_id` = `t3`.`id`) INNER JOIN `databasename`.`plugins` AS t4 ON (`t1`.`plugin_id` = `t4`.`id`) INNER JOIN `databasename`.`projecttasks` AS t5 ON (`t1`.`id` = `t5`.`task_id`) LEFT OUTER JOIN `maths`.`noperation` AS t6 ON (`t4`.`pluginname` = `t6`.`mathsname`) WHERE ((`t1`.`status` != 'pending') AND (`t1`.`sel` LIKE '1234567890')) GROUP BY `t1`.`id`) AS testme; +------+-------------+------------+--------+-----------------------------------------------------+----------------------+---------+--------------------------------+------+--------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------+--------+-----------------------------------------------------+----------------------+---------+--------------------------------+------+--------------------------------------------------------------+ | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 63 | | | 2 | DERIVED | t2 | index | PRIMARY | PRIMARY | 4 | NULL | 1 | Using index; Using temporary; Using filesort | | 2 | DERIVED | t1 | ref | PRIMARY,tasks_plugin_id,tasks_owner_id,sel_idx | tasks_owner_id | 4 | databasename.t2.id | 1 | Using where | | 2 | DERIVED | t3 | eq_ref | PRIMARY | PRIMARY | 4 | databasename.t1.created_for_id | 1 | Using index | | 2 | DERIVED | t4 | eq_ref | PRIMARY | PRIMARY | 4 | databasename.t1.plugin_id | 1 | | | 2 | DERIVED | t5 | ref | projecttasks_task_id | projecttasks_task_id | 4 | databasename.t1.id | 1 | Using index | | 2 | DERIVED | t6 | index | NULL | opname | 259 | NULL | 63 | Using where; Using index; Using join buffer (flat, BNL join) | +------+-------------+------------+--------+-----------------------------------------------------+----------------------+---------+--------------------------------+------+--------------------------------------------------------------+ DB-6 (this is wrong/slow) MariaDB [(none)]> explain SELECT COUNT(1) FROM (SELECT `t1`.`id`, `t1`.`plugin_id`, `t1`.`configuration`, `t1`.`hash`, `t1`.`run_until`, `t1`.`num_runs`, `t1`.`target_type`, `t1`.`interval`, `t1`.`created`, `t1`.`next_run`, `t1`.`latest_run`, `t1`.`status`, `t1`.`owner_id`, `t1`.`created_for_id`, `t1`.`sel`, `t1`.`filters`, `t1`.`tags` FROM `databasename`.`tasks` AS t1 INNER JOIN `databasename`.`users` AS t2 ON (`t1`.`owner_id` = `t2`.`id`) INNER JOIN `databasename`.`users` AS t3 ON (`t1`.`created_for_id` = `t3`.`id`) INNER JOIN `databasename`.`plugins` AS t4 ON (`t1`.`plugin_id` = `t4`.`id`) INNER JOIN `databasename`.`projecttasks` AS t5 ON (`t1`.`id` = `t5`.`task_id`) LEFT OUTER JOIN `maths`.`noperation` AS t6 ON (`t4`.`pluginname` = `t6`.`mathsname`) WHERE ((`t1`.`status` != 'pending') AND (`t1`.`sel` LIKE '1234567890')) GROUP BY `t1`.`id`) AS testme; +------+-------------+------------+--------+-----------------------------------------------------+----------------------+---------+--------------------------------+------+--------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------+--------+-----------------------------------------------------+----------------------+---------+--------------------------------+------+--------------------------------------------------------------+ | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 63 | | | 2 | DERIVED | t2 | index | PRIMARY | PRIMARY | 4 | NULL | 1 | Using index; Using temporary; Using filesort | | 2 | DERIVED | t1 | ref | PRIMARY,tasks_plugin_id,tasks_owner_id,sel_idx | tasks_owner_id | 4 | databasename.t2.id | 1 | Using where | | 2 | DERIVED | t3 | eq_ref | PRIMARY | PRIMARY | 4 | databasename.t1.created_for_id | 1 | Using index | | 2 | DERIVED | t4 | eq_ref | PRIMARY | PRIMARY | 4 | databasename.t1.plugin_id | 1 | | | 2 | DERIVED | t5 | ref | projecttasks_task_id | projecttasks_task_id | 4 | databasename.t1.id | 1 | Using index | | 2 | DERIVED | t6 | index | NULL | opname | 259 | NULL | 63 | Using where; Using index; Using join buffer (flat, BNL join) | +------+-------------+------------+--------+-----------------------------------------------------+----------------------+---------+--------------------------------+------+--------------------------------------------------------------+