My query is taking little time i tried the couchase advisory tool and added all the suggested index but still i am not getting the required result
My QUERY
SELECT employee.employee_id,employee.employee_email, employee.job_class,employee.magic,employee.employee_firstname, employee.employee_name,employee.employee_type,emp_sch.id, emp_sch.time_in,emp_sch.time_out,emp_sch.paycode, emp_sch.notes,emp_sch.shift_date,branch.code,branch.mall FROM stitchit_initialization_hq employee INNER JOIN stitchit_initialization_hq branch ON (branch.code = employee.branch_id OR branch.code = employee.branch_id_2 OR branch.code = employee.branch_id_3) AND branch.type=‘branch’ LEFT JOIN stitchit_data_bucket emp_sch ON emp_sch.employee_id = employee.employee_id AND branch.code = emp_sch.branch_id AND emp_sch.type = ‘employee_schedule’ AND emp_sch.shift_date BETWEEN ‘2020-04-05’ AND ‘2020-04-11’ WHERE employee.type=‘employee’ AND (employee.branch_id = ‘3221’ OR employee.branch_id_2 = ‘3221’ OR employee.branch_id_3 = ‘3221’) AND branch.code = ‘3221’ AND employee.status = ‘Active’ ORDER BY employee.employee_firstname ASC
Advisoty Indexes
“indexes”: [
{
“index_statement”: “CREATE INDEX adv_status_type_branch_id_2_branch_id_branch_id_3 ON stitchit_initialization_hq(status,type,branch_id_2,branch_id,branch_id_3)”,
“keyspace_alias”: “stitchit_initialization_hq_employee”,
“recommending_rule”: “Index keys follow order of predicate types: 1. Common leading key for disjunction (2. equality/null/missing, 2. equality/null/missing), 2. equality/null/missing.”
},
{
“index_statement”: “CREATE INDEX adv_type_code ON stitchit_initialization_hq(type,code)”,
“keyspace_alias”: “stitchit_initialization_hq_branch”,
“recommending_rule”: “Index keys follow order of predicate types: 1. Common leading key for disjunction (2. equality/null/missing, 2. equality/null/missing).”
},
{
“index_statement”: “CREATE INDEX adv_type_shift_date_branch_id_employee_id ON stitchit_data_bucket(type,shift_date,branch_id,employee_id)”,
“keyspace_alias”: “stitchit_data_bucket_emp_sch”,
“recommending_rule”: “Index keys follow order of predicate types: 2. equality/null/missing, 4. not less than/between/not greater than, 9. non-static join predicate.”
}
]
SELECT e.employee_id, e.employee_email, e.job_class, e.magic, e.employee_firstname, e.employee_name, e.employee_type,
es.id, es.time_in, es.time_out, es.paycode, es.notes, es.shift_date, b.code, b.mall
FROM stitchit_initialization_hq AS e
INNER JOIN stitchit_initialization_hq AS b ON b.code IN [e.branch_id, e.branch_id_2, e.branch_id_3] AND b.type = "branch"
LEFT JOIN stitchit_data_bucket AS es ON es.employee_id = e.employee_id AND b.code = es.branch_id
AND es.type = "employee_schedule"
AND es.shift_date BETWEEN "2020-04-05" AND "2020-04-11"
WHERE e.type="employee" AND ANY v IN [e.branch_id, e.branch_id_2, e.branch_id_3] SATISFIES v = "3221" END
AND b.code = "3221" AND e.status = "Active"
ORDER BY e.employee_firstname ASC;
CREATE INDEX ix1 ON stitchit_initialization_hq(status, DISTINCT [branch_id, branch_id_2, branch_id_3])
WHERE type = "employee";
CREATE INDEX ix2 ON stitchit_initialization_hq(code, mall) WHERE type = "branch";
CREATE INDEX ix3 ON stitchit_data_bucket(employee_id, branch_id, shift_date, id, time_in, time_out, paycode, notes) WHERE type = "employee_schedule";
Thanks but there is a syntax error i am not be able to find
{
“code”: 3000,
“msg”: “syntax error - at SATISFILES”,
“query_from_user”: “SELECT e.employee_id, e.employee_email, e.job_class, e.magic, e.employee_firstname,\r\ne.employee_name, e.employee_type,es.id, es.time_in, es.time_out,\r\nes.paycode, es.notes, es.shift_date, b.code, b.mall\r\nFROM stitchit_initialization_hq AS e\r\nINNER JOIN stitchit_initialization_hq AS b ON b.code IN [e.branch_id, e.branch_id_2, e.branch_id_3] AND b.type = ‘branch’\r\nLEFT JOIN stitchit_data_bucket AS es ON es.employee_id = e.employee_id AND b.code = es.branch_id\r\nAND es.type = ‘employee_schedule’\r\nAND es.shift_date BETWEEN ‘2020-04-05’ AND ‘2020-04-11’\r\nWHERE e.type=‘employee’ AND ANY v IN [e.branch_id, e.branch_id_2, e.branch_id_3] SATISFILES v = ‘3221’ END\r\nAND b.code = ‘3221’ AND e.status = ‘Active’\r\nORDER BY e.employee_firstname ASC”
}
]
