MariaDB 10.2.22 Galera Cluster Using Bad Indexes After Analyze

Question

I'm using Drupal on a MariaDB Galera 10.2.22 3 node cluster and I'm having some problems with one query. I have a table (InnoDB) called field_data_field_department which has a bunch of indexes. If I dump the 5 tables used in my query and load them into a new DB, the query runs fine using the index field_department_target_id. But once I analyze that table (or MariaDB does an internal stats rebuild) the query stops using that index and uses the PRIMARY index. I don't understand why it does this, and the query is MUCH slower using PRIMARY (90-120 seconds vs under 1/10th of a second.)

Does anyone know how I can diagnose why the planner is picking the wrong index? Or why ANALYZEing the table makes it break (I would think that would make things better) The query is dynamically generated by Drupal so I can't just go into the code and add FORCE INDEX.

Here's the explain for the fast result:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  file_managed    ref uri,status,file_type    status  1   const   7403    Using where; Using temporary; Using filesort
1   SIMPLE  field_data_field_department ref PRIMARY,entity_type,deleted,entity_id   PRIMARY 391 const,drupal_authoring.file_managed.fid,const   1   Using where
1   SIMPLE  taxonomy_term_data_field_data_field_department  eq_ref  PRIMARY PRIMARY 4   drupal_authoring.field_data_field_department.field_department_target_id 1   Using where; Using index
1   SIMPLE  taxonomy_term_data_field_data_field_department__field_data_field_department ref PRIMARY,entity_type,deleted,field_department_target_id  field_department_target_id  390 drupal_authoring.taxonomy_term_data_field_data_field_department.tid,const   15  Using where; Using index
1   SIMPLE  file_usage  ref PRIMARY,fid_count,fid_module    fid_count   4   drupal_authoring.file_managed.fid   1   Using index

And for the slow query:

id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
1       SIMPLE  file_managed    ref     uri,status,file_type    file_type       152     const   7592    Using index condition; Using where; Using temporary; Using filesort
1       SIMPLE  field_data_field_department     ref     PRIMARY,entity_type,deleted,entity_id   PRIMARY 391     const,drupal_authoring.file_managed.fid,const     1       Using where
1       SIMPLE  taxonomy_term_data_field_data_field_department  eq_ref  PRIMARY PRIMARY 4       drupal_authoring.field_data_field_department.field_department_target_id   1  Using where; Using index
1       SIMPLE  taxonomy_term_data_field_data_field_department__field_data_field_department     ref     PRIMARY,entity_type,deleted,field_department_target_id  PRIMARY 386     const       7985    Using where
1       SIMPLE  file_usage      ref     PRIMARY,fid_count,fid_module    fid_count       4       drupal_authoring.file_managed.fid 1       Using index

And here is the output of SHOW INDEXES

Table   Non_unique  Key_name    Seq_in_index    Column_name Collation   Cardinality Sub_part    Packed  Null    Index_type  Comment Index_comment
field_data_field_department 0   PRIMARY 1   entity_type A   4   NULL    NULL        BTREE       
field_data_field_department 0   PRIMARY 2   entity_id   A   15742   NULL    NULL        BTREE       
field_data_field_department 0   PRIMARY 3   deleted A   15742   NULL    NULL        BTREE       
field_data_field_department 0   PRIMARY 4   delta   A   15742   NULL    NULL        BTREE       
field_data_field_department 0   PRIMARY 5   language    A   15742   NULL    NULL        BTREE       
field_data_field_department 1   entity_type 1   entity_type A   4   NULL    NULL        BTREE       
field_data_field_department 1   bundle  1   bundle  A   24  NULL    NULL        BTREE       
field_data_field_department 1   deleted 1   deleted A   2   NULL    NULL        BTREE       
field_data_field_department 1   entity_id   1   entity_id   A   15742   NULL    NULL        BTREE       
field_data_field_department 1   revision_id 1   revision_id A   15742   NULL    NULL    YES BTREE       
field_data_field_department 1   language    1   language    A   2   NULL    NULL        BTREE       
field_data_field_department 1   field_department_target_id  1   field_department_target_id  A   715 NULL    NULL        BTREE       

Any ideas? Even if I can just do something to stop MariaDB from analyzing the table that would help. I can dump and reload it as a temp fix, but that only lasts for a little while.

Answer 1

We ended up solving this by updating the Drupal relationships of the CCK. Removing the node table from the form.