Details
-
Bug
-
Resolution: Fixed
-
Major
-
6.6.0, Cheshire-Cat
-
Untriaged
-
1
-
Unknown
Description
Build : 7.0.0-2073 & 6.6.0-7637
Query :
ADVISE SELECT `type`,`city`,count
FROM `travel-sample` use index(using gsi) where city is not null and lower(`type`) != "airport" and upper(`type`) !="landmark" GROUP BY `type`,city t order by `type`,city LIMIT 100 OFFSET 500
For this query, there is no covering index recommendation.
[
|
{
|
"#operator": "Advise",
|
"advice": {
|
"#operator": "IndexAdvice",
|
"adviseinfo": {
|
"current_indexes": [
|
{
|
"index_statement": "CREATE INDEX def_type ON `travel-sample`(`type`)",
|
"keyspace_alias": "travel-sample"
|
},
|
{
|
"index_statement": "CREATE INDEX def_city ON `travel-sample`(`city`)",
|
"keyspace_alias": "travel-sample"
|
}
|
],
|
"recommended_indexes": {
|
"indexes": [
|
{
|
"index_statement": "CREATE INDEX adv_upper_type_lower_type_city ON `travel-sample`(`city`,lower((`type`)),upper((`type`)))",
|
"keyspace_alias": "travel-sample",
|
"recommending_rule": "Index keys follow order of predicate types: 1. Common leading key for disjunction (5. less than/greater than, 5. less than/greater than, 7. not null/not missing/valued)."
|
}
|
]
|
}
|
}
|
},
|
"query": "SELECT `type`,`city`,count(*) FROM `travel-sample` use index(using gsi) where city is not null and lower(`type`) != \"airport\" and upper(`type`)!=\"landmark\" GROUP BY `type`,city t order by `type`,city LIMIT 100 OFFSET 500"
|
}
|
]
|
Same query without the index hint,
ADVISE SELECT `type`,`city`,count FROM `travel-sample` where city is not null and lower(`type`) Unable to render embedded object: File (= "airport" and upper(`type`)) not found.="landmark" GROUP BY `type`,city t order by `type`,city LIMIT 100 OFFSET 500
has a covering index recommendation.
[
|
{
|
"#operator": "Advise",
|
"advice": {
|
"#operator": "IndexAdvice",
|
"adviseinfo": {
|
"current_indexes": [
|
{
|
"index_statement": "CREATE INDEX def_type ON `travel-sample`(`type`)",
|
"keyspace_alias": "travel-sample"
|
},
|
{
|
"index_statement": "CREATE INDEX def_city ON `travel-sample`(`city`)",
|
"keyspace_alias": "travel-sample"
|
}
|
],
|
"recommended_indexes": {
|
"covering_indexes": [
|
{
|
"index_statement": "CREATE INDEX adv_upper_type_lower_type_city_type ON `travel-sample`(upper((`type`)),lower((`type`)),`city`,`type`)",
|
"keyspace_alias": "travel-sample"
|
}
|
],
|
"indexes": [
|
{
|
"index_statement": "CREATE INDEX adv_upper_type_lower_type_city ON `travel-sample`(`city`,lower((`type`)),upper((`type`)))",
|
"keyspace_alias": "travel-sample",
|
"recommending_rule": "Index keys follow order of predicate types: 1. Common leading key for disjunction (5. less than/greater than, 5. less than/greater than, 7. not null/not missing/valued)."
|
}
|
]
|
}
|
}
|
},
|
"query": "SELECT `type`,`city`,count(*) FROM `travel-sample` where city is not null and lower(`type`) != \"airport\" and upper(`type`)!=\"landmark\" GROUP BY `type`,city t order by `type`,city LIMIT 100 OFFSET 500"
|
}
|
]
|