Details
-
Bug
-
Resolution: Fixed
-
Major
-
6.6.0, Cheshire-Cat
-
7.0.0-2169
-
Untriaged
-
1
-
Unknown
Description
I have these buckets in the system:
multiple_table_db_79919484_simple_table_1
multiple_table_db_79919484_simple_table_10
multiple_table_db_79919484_simple_table_2
multiple_table_db_79919484_simple_table_3
multiple_table_db_79919484_simple_table_4
Running advise on this query
ADVISE SELECT t_1.int_field1 , t_1.decimal_field1 , t_1.primary_key_id , t_1.bool_field1 FROM bucket_01 t_1 LEFT JOIN bucket_05 t_1SiRAdlidCB ON ( t_1.primary_key_id = t_1SiRAdlidCB.primary_key_id ) LEFT JOIN bucket_010 t_1fLjtGGSnKf ON ( t_1.primary_key_id = t_1fLjtGGSnKf.primary_key_id ) INNER JOIN bucket_04 t_1GHMwUzdukS ON ( t_1.primary_key_id = t_1GHMwUzdukS.primary_key_id ) INNER JOIN bucket_04 t_1LjVrlSNfDc ON ( t_1.primary_key_id = t_1LjVrlSNfDc.primary_key_id ) WHERE ((t_1.primary_key_id IS NOT NULL AND t_1.int_field1 IS NULL)) OR ((t_1.int_field1 >= 47635891 OR t_1.varchar_field1 IS NOT NULL));
Gets this recommendation
Covered Index Recommendations
CREATE INDEX adv_primary_key_id ON `bucket_05`(`primary_key_id`)
CREATE INDEX adv_primary_key_id ON `bucket_010`(`primary_key_id`)
CREATE INDEX adv_primary_key_id ON `bucket_04`(`primary_key_id`)
CREATE INDEX adv_primary_key_id ON `bucket_04`()
Index Recommendations
CREATE INDEX adv_int_field1_primary_key_id ON `bucket_01`(`int_field1`,`primary_key_id`)
CREATE INDEX adv_varchar_field1 ON `bucket_01`(`varchar_field1`)
There is one blank index being recommended, plus it says those indexes are covering but they do not include fields from the predicate?
Attachments
Issue Links
- relates to
-
MB-37116 Index advisor to combine information for duplicate index definition
- Closed