An Index for Both Conditions
MediumDescription
This query finds the lines of product 19 with 2 or more units:
CodeSELECT * FROM order_items WHERE product_id = 19 AND quantity >= 2;
order_items already has an index on product_id, so the database finds product 19's lines
through it and then checks quantity row by row. Create an index named
idx_items_product_qty on two columns that lets the database answer both conditions from the
index. Then show the plan of the query above, without the estimated costs.
Tables
| order_items |
|---|
| item_id (PK) |
| order_id (FK) |
| product_id (FK) |
| quantity |
| unit_price |
| discount_pct |