Details
-
Bug
-
Resolution: Won't Fix
-
Major
-
Cheshire-Cat
-
Untriaged
-
Unknown
Description
Build : 7.0.0-1824
Data used : Employee dataset.
Sample Doc
{
|
"name": "Kory Palmer",
|
"salary": 75891.68,
|
"type": "emp",
|
"_id": "emp10000001",
|
"mutated": 0,
|
"manages": {
|
"team_size": 6,
|
"reports": [
|
"Hedda Wright",
|
"Callia Robinson Sr.",
|
"Trista Baker III",
|
"Joby Allen",
|
"Basha Foster",
|
"Salina Allen"
|
]
|
},
|
"emp_id": "10000001",
|
"dept": "Engineering",
|
"email": "kory@mcdiabetes.com",
|
"join_date": "2015-08-13T09:30:00",
|
"languages_known": [
|
"Quechua",
|
"Portuguese",
|
"Africans"
|
],
|
"is_manager": true
|
}
|
FTS index
type mapping = emp
fields in index = dept (keyword analyzer), name (keyword analyzer), salary (number), join_date (date field), is_manager (boolean field)
definition attached
GSI secondary index : CREATE INDEX `idx1` ON `default`(`type`,`dept`,`name`,`salary`)
Query
select name, salary, join_date from default use index(using fts) where (name = "Kory Palmer" or dept="Engineering" ) and `type`="emp" and (salary > 60000 and salary < 90000) and is_manager order by name
|
This query is not sargable.
However this query is sargable -
select name, salary, join_date from default use index(using fts) where (name = "Kory Palmer" or dept="Engineering" ) and `type`="emp" and (salary > 60000 and salary < 90000) and is_manager=True order by name
|