SO I have Mysql and MariaDB running in two different boxes. I know that MariaDB isnt supported but I refuse to believe this is something that mariaDB cant do. Its just an Accounts Listview.
When I run an explain on a query in MariaDB I get this. You will see in several of the keys it picks none (NULL). It doesnt do this in MySQL. The databases and indexes are identical (both imported from the same file) and yet on the MariaDB this query takes more than 22 minutes to run and on the MySQL database it runs in less than 1 second.
I know I have run SugarCRM on MariaDB before but this time it simply doesnt work and I cant figrure out why.
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
---|---|---|---|---|---|---|---|---|---|---|
1 | SIMPLE | accounts | ref | idx_accnt_assigned_del | idx_accnt_assigned_del | 2 | const | 79677 | Using temporary; Using filesort | |
1 | SIMPLE | sf_accounts | ref|filter | idx_favs_module_record_deleted,idx_favs_id_record_... | idx_favs_id_record_id|idx_sugarfavorites_assigned_... | 145|147 | star2star.accounts.id | 1 (0%) | Using where; Using rowid filter | |
1 | SIMPLE | accounts_cstm | eq_ref | PRIMARY | PRIMARY | 144 | star2star.accounts.id | 1 | ||
1 | SIMPLE | accounts_following | ref|filter | idx_subscriptions_created_by,idx_subscriptions_par... | idx_subscription_parent|idx_subscriptions_created_... | 144|145 | star2star.accounts.id | 1 (0%) | Using where; Using rowid filter | |
1 | SIMPLE | jt1_following_link | eq_ref | PRIMARY | PRIMARY | 144 | star2star.accounts_following.created_by | 1 | Using where | |
1 | SIMPLE | jt2_assigned_user_link | ALL | PRIMARY | NULL | NULL | NULL | 602 | Using where; Using join buffer (flat, BNL join) | |
1 | SIMPLE | accounts_email_addresses_primary | ref | idx_bean_id | idx_bean_id | 547 | star2star.accounts.id,const | 1 | Using where | |
1 | SIMPLE | jt3_email_addresses_primary | ref | PRIMARY,idx_ea_del_ea_id | idx_ea_del_ea_id | 2 | const | 246001 | Using where; Using index | |
1 | SIMPLE | jt3_email_addresses_primary_erased | ALL | PRIMARY | NULL | NULL | NULL | 1 | Using where; Using join buffer (flat, BNL join) | |
1 | SIMPLE | jt4_assigned_rad_c | ALL | PRIMARY | NULL | NULL | NULL | 602 | Using where; Using join buffer (incremental, BNL j... | |
1 | SIMPLE | jt5_assigned_spd_c | eq_ref | PRIMARY | PRIMARY | 144 | star2star.accounts_cstm.user_id5_c | 1 | Using where | |
1 | SIMPLE | jt6_assigned_nam_c | eq_ref | PRIMARY | PRIMARY | 144 | star2star.accounts_cstm.user_id4_c | 1 | Using where | |
1 | SIMPLE | jt7_cssg_c | eq_ref | PRIMARY | PRIMARY | 144 | star2star.accounts_cstm.user_id1_c | 1 | Using where | |
1 | SIMPLE | jt8_cssg_2_c | eq_ref | PRIMARY | PRIMARY | 144 | star2star.accounts_cstm.user_id6_c | 1 | Using where | |
1 | SIMPLE | jt9_cssg_3_c | eq_ref | PRIMARY | PRIMARY | 144 | star2star.accounts_cstm.user_id7_c | 1 | Using where | |
1 | SIMPLE | jt10_assigned_sales_engr_c | eq_ref | PRIMARY | PRIMARY | 144 | star2star.accounts_cstm.user_id8_c | 1 | Using where | |
1 | SIMPLE | jt11_assigned_sales_engr2_c | eq_ref | PRIMARY | PRIMARY | 144 | star2star.accounts_cstm.user_id9_c | 1 | Using where | |
1 | SIMPLE | jt12_om_rep_1_c | eq_ref | PRIMARY | PRIMARY | 144 | star2star.accounts_cstm.user_id12_c | 1 | Using where | |
1 | SIMPLE | accounts_accounts_1 | ref | accounts_accounts_1_alt | accounts_accounts_1_alt | 147 | star2star.accounts.id | 1 | Using where | |
1 | SIMPLE | jt13_accounts_accounts_1_right | eq_ref | PRIMARY,idx_accounts_id_del,idx_accnt_assigned_del | PRIMARY | 144 | star2star.accounts_accounts_1.accounts_accounts_1a... | 1 | Using where | |
1 | SIMPLE | jt14_member_of | eq_ref | PRIMARY,idx_accounts_id_del,idx_accnt_assigned_del | PRIMARY | 144 | star2star.accounts.parent_id | 1 | Using where | |
1 | SIMPLE | jt15_partner_success_manager_c | eq_ref | PRIMARY | PRIMARY | 144 | star2star.accounts_cstm.user_id15_c | 1 | Using where | |
1 | SIMPLE | jt16_partner_success_tech_c | eq_ref | PRIMARY | PRIMARY | 144 | star2star.accounts_cstm.user_id16_c | 1 | Using where | |
1 | SIMPLE | jt17_created_by_link | eq_ref | PRIMARY | PRIMARY | 144 | star2star.accounts.created_by | 1 | Using where | |
1 | SIMPLE | jt18_modified_user_link | eq_ref | PRIMARY | PRIMARY | 144 | star2star.accounts.modified_user_id | 1 | Using where | |
1 | SIMPLE | accounts_favorite | ref | idx_favs_module_record_deleted,idx_favs_id_record_... | idx_favs_module_record_deleted | 350 | const,star2star.accounts.id,const | 1 | Using where | |
1 | SIMPLE | jt19_favorite_link | eq_ref | PRIMARY | PRIMARY | 144 | star2star.accounts_favorite.modified_user_id | 1 | Using where |