AnsweredAssumed Answered

MySQL and MariaDB Indexes

Question asked by Kenneth Brill on Dec 8, 2019

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
1SIMPLEaccountsrefidx_accnt_assigned_delidx_accnt_assigned_del2const79677Using temporary; Using filesort
1SIMPLEsf_accountsref|filteridx_favs_module_record_deleted,idx_favs_id_record_...idx_favs_id_record_id|idx_sugarfavorites_assigned_...145|147star2star.accounts.id1 (0%)Using where; Using rowid filter
1SIMPLEaccounts_cstmeq_refPRIMARYPRIMARY144star2star.accounts.id1
1SIMPLEaccounts_followingref|filteridx_subscriptions_created_by,idx_subscriptions_par...idx_subscription_parent|idx_subscriptions_created_...144|145star2star.accounts.id1 (0%)Using where; Using rowid filter
1SIMPLEjt1_following_linkeq_refPRIMARYPRIMARY144star2star.accounts_following.created_by1Using where
1SIMPLEjt2_assigned_user_linkALLPRIMARY NULL NULL NULL 602Using where; Using join buffer (flat, BNL join)
1SIMPLEaccounts_email_addresses_primaryrefidx_bean_ididx_bean_id547star2star.accounts.id,const1Using where
1SIMPLEjt3_email_addresses_primaryrefPRIMARY,idx_ea_del_ea_ididx_ea_del_ea_id2const246001Using where; Using index
1SIMPLEjt3_email_addresses_primary_erasedALLPRIMARY NULL NULL NULL 1Using where; Using join buffer (flat, BNL join)
1SIMPLEjt4_assigned_rad_cALLPRIMARY NULL NULL NULL 602Using where; Using join buffer (incremental, BNL j...
1SIMPLEjt5_assigned_spd_ceq_refPRIMARYPRIMARY144star2star.accounts_cstm.user_id5_c1Using where
1SIMPLEjt6_assigned_nam_ceq_refPRIMARYPRIMARY144star2star.accounts_cstm.user_id4_c1Using where
1SIMPLEjt7_cssg_ceq_refPRIMARYPRIMARY144star2star.accounts_cstm.user_id1_c1Using where
1SIMPLEjt8_cssg_2_ceq_refPRIMARYPRIMARY144star2star.accounts_cstm.user_id6_c1Using where
1SIMPLEjt9_cssg_3_ceq_refPRIMARYPRIMARY144star2star.accounts_cstm.user_id7_c1Using where
1SIMPLEjt10_assigned_sales_engr_ceq_refPRIMARYPRIMARY144star2star.accounts_cstm.user_id8_c1Using where
1SIMPLEjt11_assigned_sales_engr2_ceq_refPRIMARYPRIMARY144star2star.accounts_cstm.user_id9_c1Using where
1SIMPLEjt12_om_rep_1_ceq_refPRIMARYPRIMARY144star2star.accounts_cstm.user_id12_c1Using where
1SIMPLEaccounts_accounts_1refaccounts_accounts_1_altaccounts_accounts_1_alt147star2star.accounts.id1Using where
1SIMPLEjt13_accounts_accounts_1_righteq_refPRIMARY,idx_accounts_id_del,idx_accnt_assigned_delPRIMARY144star2star.accounts_accounts_1.accounts_accounts_1a...1Using where
1SIMPLEjt14_member_ofeq_refPRIMARY,idx_accounts_id_del,idx_accnt_assigned_delPRIMARY144star2star.accounts.parent_id1Using where
1SIMPLEjt15_partner_success_manager_ceq_refPRIMARYPRIMARY144star2star.accounts_cstm.user_id15_c1Using where
1SIMPLEjt16_partner_success_tech_ceq_refPRIMARYPRIMARY144star2star.accounts_cstm.user_id16_c1Using where
1SIMPLEjt17_created_by_linkeq_refPRIMARYPRIMARY144star2star.accounts.created_by1Using where
1SIMPLEjt18_modified_user_linkeq_refPRIMARYPRIMARY144star2star.accounts.modified_user_id1Using where
1SIMPLEaccounts_favoriterefidx_favs_module_record_deleted,idx_favs_id_record_...idx_favs_module_record_deleted350const,star2star.accounts.id,const1Using where
1SIMPLEjt19_favorite_linkeq_refPRIMARYPRIMARY144star2star.accounts_favorite.modified_user_id1Using where

Outcomes