Adaptive Index is not working


FROM stitchit_initialization_hq schedule USE KEYS 'schedule_3221190011' 
UNNEST schedule.schedule_conditions as v 
INNER JOIN stitchit_initialization_hq item_specifics ON item_specifics.item_sp_id = v.parent_item_id 

AND item_specifics.type = 'item_specifics' 
AND item_specifics.item_id = '3221190599' 
WHERE schedule.type='schedule' 
AND v.condition_status = 'Active' 
ORDER BY item_specifics.item_sp_id ASC

//WANT TO USE THIS INDEX while key_Scan in document this schedule_3221190011

CREATE INDEX `ai_airport_day_faa`
ON  stitchit_initialization_hq (DISTINCT PAIRS({condition_status, condittion_price}))
WHERE type = "schedule";

USE KEYS on keyspace DOES NOT required any index and even qualified index is present it will not use it.
USE KEYS means you are providing document keys . Index Scan is waste of time ( index means you are scanning index and produce document keys) . Uses the document keys to do Fetch of the document.

I would recommend read the documentation.

1 Like