Indexing a small table increases the query cost

[Aram Mirzadeh]

Hi,

Running on 5.7.20, I have a query which runs often (every 10-15 seconds) and is giving me some weird results from some rather small tables ... data = 1400 rows, product 30, status = 70.

SELECT * FROM `data
JOIN status ON status_fk = status.id AND status.active
JOIN product on product_fk = product.id AND product.active
WHERE status.status IN ('a','b','c','d');

ALTER TABLE `product` ADD KEY `idx_active` (`active`);
ALTER TABLE `status` ADD KEY `idx_active` (`active`);

Query Cost: 3255

ALTER TABLE `product` DROP KEY `idx_active`;
ALTER TABLE `status` DROP KEY `idx_active`;

Query Cost: 242

Is it because we're indexing small datasets that the cost of the query jumps so high when there is an index?

Thanks.