How to force YDB to use table index, not to read all rows.

[vladd11]

Hello. I have this table:

CREATE TABLE `order_items`
(
    `id` String,
    `order_id` String,
    `product_id` String,
    `quantity` Uint32,
    PRIMARY KEY (`id`)
);

with index idx_order_id_quantity -> order_id, quantity

I'd like to get quantity by order_id, so I use this query:

SELECT quantity
FROM order_items
WHERE order_id=""

But it scans all rows in table instead of using my index.

Explanation

{
    "plan": {
        "meta": {
            "type": "script",
            "version": "0.2"
        },
        "queries": [
            {
                "tables": [
                    {
                        "name": "/ru-central1/b1gnjh1pn5tbeqdb2lqt/etn4ge82u3bfv9qldrbk/order_items",
                        "reads": [
                            {
                                "columns": [
                                    "id",
                                    "order_id"
                                ],
                                "scan_by": [
                                    "id"
                                ],
                                "type": "FullScan"
                            }
                        ]
                    }
                ]
            }
        ]
    }
}

[snaury]

You need to explicitly specify which index you want YDB to use in your query, which in your case would probably be something like SELECT quantity FROM order_items VIEW idx_order_id_quantity WHERE order_id = .... Please see https://ydb.tech/en/docs/best_practices/secondary_indexes for more details.

[vladd11]

Oh, thanks, it works.