MySQL Forums
Forum List  »  Optimizer & Parser

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

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Indexing a small table increases the query cost
344
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.