CODEWITHSUNDEEP
mysqldatabase-performance
vondip
vondip

Reputation: 14039

Mysql - Why does count on an indexed column takes so long?

I a simple query, counting number of items. The table items has 1.5M records. From some reason, running the query takes 65 seconds! The query returns 30,000 

SELECT count(*)
FROM `items`
WHERE `items`.`owner_id`
  IN (1, 2, 3, 4, 5, 6, 7, 8)
AND items.is_hidden = false

EDIT: added the 'and' clause to fully describe problem

Explain shows that the query is using this index:

1   SIMPLE  items   range   idx_owner   idx_owner   4   NULL    258 Using index condition; Using where

And these are the table's indexes:

items   0   PRIMARY 1   id  A   1444298 NULL    NULL        BTREE       
items   1   items_a951d5d6  1   slug    A   288859  767 NULL        BTREE       
items   1   category_id_refs_id_3b77a81e    1   category_id A   34  NULL    NULL    YES BTREE       
items   1   origin_id_refs_id_99b3fd12  1   origin_id   A   2   NULL    NULL    YES BTREE       
items   1   parent_id_refs_id_99b3fd12  1   parent_id   A   6   NULL    NULL    YES BTREE       
items   1   name    1   name    A   1444298 NULL    NULL        BTREE       
items   1   idx_owner   1   owner_id    A   722149  NULL    NULL        BTREE

Why is it taking so long? Any way to improve it?

Upvotes: 0

Views: 187

Answers (2)

Benjamin Paap
Benjamin Paap

Reputation: 2759

Try creating an index of both columns:

CREATE INDEX idx_both_columns ON `items` (`owner_id`, `is_hidden`);

Upvotes: 1

Rimas
Rimas

Reputation: 6024

Try to use temporary table and JOIN:

CREATE TEMPORARY TABLE owner_ids (id INT PRIMARY KEY);
INSERT INTO owner_ids VALUES (1),(2),(3),(4),(5),(6),(7),(8);

SELECT count(*)
FROM items
JOIN owner_ids ON owner_ids.id = items.owner_id

Upvotes: 1

Related Questions