I would expect the query below to use only the IDX_AUDIT6 index, but when i explain it does an intersect with the doc_type index. Can somebody explain to me why?
select acxiom.errors.error_code, id, timestamp
from order_history
where doc_type = “other” and acxiom.outgoing_request.activityTypeCode
and acxiom.errors.developer_message like "%Timeout_%" and timestamp > “+ timeParam +”;
CREATE INDEX IDX_AUDIT6 ON order_history((doc_type,(acxiom.errors.error_code),(acxiom.errors.developer_message),_id,timestamp))
WHERE ((doc_type = “other”) and ((acxiom.errors.developer_message) is not null) and ((acxiom.outgoing_request.activityTypeCode) is not null)) WITH { “num_replica”:1 }
the other index it is also using:
CREATE INDEX doc_type ON order_history(doc_type) WITH { “defer_build”:true, “num_replica”:1 }
What version of CB? Post the EXPLAIN. doc_type index is subset of others it should have ignored.
The following is right index for query (check id or _id)
CREATE INDEX IDX_AUDIT6 ON order_history(acxiom.outgoing_request.activityTypeCode,
timestamp,
acxiom.errors.developer_message,
acxiom.errors.error_code, id)
WHERE doc_type = "other";
It looks like you are using old version of CB. Fixed by MB-26483
Option 1) Specify index hint avoid IntersectScan. Example : FROM order_history USE INDEX (IDX_AUDIT6)
Option 2) Upgrade CB 6.0 and try it.