MySQL Forums
Forum List  »  Optimizer & Parser

Indexing a small table increases the query cost
Posted by: Aram Mirzadeh
Date: February 28, 2018 04:52AM


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.

JOIN status ON status_fk = AND
JOIN product on product_fk = AND
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?


Options: ReplyQuote

Written By
Indexing a small table increases the query cost
February 28, 2018 04:52AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.