Details
-
Bug
-
Resolution: Fixed
-
Critical
-
6.5.0
-
Untriaged
-
Centos 64-bit
-
Unknown
Description
Build: 6.5.0 build 4282.
Standard `travel-sample` bucket.
No primary/secondary indexes for this bucket.
I'm trying to run the following query:
DELETE FROM `travel-sample` WHERE ((free_breakfast=true OR city is null)) OR (id <= "549"); |
The answer is totally predictable:
[
|
{
|
"code": 4000, |
"msg": "No index available on keyspace travel-sample that matches your query. Use CREATE INDEX or CREATE PRIMARY INDEX to create an index, or check that your expected index is online.", |
"query": "DELETE FROM `travel-sample` WHERE ((free_breakfast=true OR city is null)) OR (id <= \"549\");" |
}
|
]
|
Running this:
advise DELETE FROM `travel-sample` WHERE ((free_breakfast=true OR city is null)) OR (id <= "549"); |
|
got this:
Index Recommendations
|
CREATE INDEX adv_free_breakfast ON `travel-sample`(`free_breakfast`)
|
CREATE INDEX adv_id ON `travel-sample`(`id`)
|
After executing those recommendations, I got 2 indexes:
CREATE INDEX `adv_free_breakfast` ON `travel-sample`(`free_breakfast`)
|
CREATE INDEX `adv_id` ON `travel-sample`(`id`)
|
Trying to run DELETE once again:
[
|
{
|
"code": 4000, |
"msg": "No index available on keyspace travel-sample that matches your query. Use CREATE INDEX or CREATE PRIMARY INDEX to create an index, or check that your expected index is online.", |
"query": "DELETE FROM `travel-sample` WHERE ((free_breakfast=true OR city is null)) OR (id <= \"549\");" |
}
|
]
|
Advise gives me this:
Index Recommendations
|
CREATE INDEX adv_free_breakfast ON `travel-sample`(`free_breakfast`)
|
CREATE INDEX adv_id ON `travel-sample`(`id`)
|
But, I already have those 2 indexes.
If I will change DELETE query a little bit (I've changed `city` field condition):
DELETE FROM `travel-sample` WHERE ((free_breakfast=true OR city ="AAA")) OR (id <= "549"); |
I will be provided with correct set of recommended indexes:
Index Recommendations
|
CREATE INDEX adv_free_breakfast ON `travel-sample`(`free_breakfast`)
|
CREATE INDEX adv_city ON `travel-sample`(`city`)
|
CREATE INDEX adv_id ON `travel-sample`(`id`)
|
Attachments
For Gerrit Dashboard: MB-36062 | ||||||
---|---|---|---|---|---|---|
# | Subject | Branch | Project | Status | CR | V |
115009,3 | MB-36062 fix the bug in index recommendation rules | master | query-ee | Status: MERGED | +2 | +1 |
115449,3 | MB-36062 Add index key for ISNULL/ISMISSING predicates For ISNULL predicate, add the field name in to index keys. For ISMISSING predicate, add "field IS MISSING" to index keys. If this index key happens to be the leading key, add it to index condition too. | master | query-ee | Status: MERGED | +2 | +1 |
115450,3 | MB-36062 Add testcases | master | query | Status: MERGED | +2 | +1 |