MariaDB 10.2.22 Galera Cluster Using Bad Indexes After Analyze

galeraindexmariadbmariadb-10.2

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.

Best Answer

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

Related Solutions

Mysql – InnoDB – Use combined index with primary key on GROUP BY

Because you only select columns from table_1 and because table_2 is only joined (on the right side) of the LEFT JOIN and none of its columns is used elsewhere (SELECT, WHERE, GROUP BY or ORDER BY clauses), you can completely remove the LEFT JOIN table_2 t2 on t1.id = t2.refid part.

This might have given less rows in the result but because you group by the Primary key of table_1 after the join, there is no such case.

SELECT t1.a 
FROM table_1 t1 
WHERE t1.b = 99
GROUP BY t1.id
ORDER BY t1.c ;

Now, because you group by the Primary key of table_1, which is redundant when you have only one table, you can also remove that part: GROUP BY t1.id

Finally, the query is equivalent to:

SELECT t1.a 
FROM table_1 t1 
WHERE t1.b = 99
ORDER BY t1.c ;

which should use the index on (b, c), which yes, includes the primary key. You may consider it to be (b, c, id) if you want.

But whether that index will be used is not 100% sure. Depending on the selectivity of column b (what percent of the whole table has b=99?) this index may or may not be used. If there a lot of rows with b=99 (a large percentage), the optimizer may choose to scan the whole table instead and do a filesort, than use the index, select those (99,c) combinations that exist and then hit the table to find the a values.

If you have an index on (b, c, a) or on (b, c, id, a), the query will be able to find all the info that is needed in this index and in the correct order, so it will use it.

Mysql – Awful execution plans after migrating from MySQL 5.5.11 to MariaDB 5.5.41

Well, this is a bit of voodoo/shotgun debugging, but I've got it functioning okay for the time being. I set the extended_keys option to on, and also created a covering index on the 8-million-row table in question. Now I'm getting an execution plan that's even better than the acceptable plan the old server was coming up with (which was only using Magento's default indexes).

Still, it all seems a bit hand-wavey, so I'll leave this open for now in case anybody has a better recommendation or explanation.

Best Answer

Related Solutions

Mysql – InnoDB – Use combined index with primary key on GROUP BY

Mysql – Awful execution plans after migrating from MySQL 5.5.11 to MariaDB 5.5.41

Related Question