MySQL 5.5 "select distinct" is really slow

Question

One of the things my app does a fair amount is:

select count(distinct id) from x;

with id the primary key for table x. With MySQL 5.1 (and 5.0), it looks like this:

mysql> explain SELECT count(distinct id) from x;
+----+-------------+----------+-------+---------------+-----------------+---------+------+---------+-------------+
| id | select_type | table    | type  | possible_keys | key             | key_len | ref  | rows    | Extra       |
+----+-------------+----------+-------+---------------+-----------------+---------+------+---------+-------------+
|  1 | SIMPLE      | x        | index | NULL          | ix_blahblahblah | 1       | NULL | 1234567 | Using index |
+----+-------------+----------+-------+---------------+-----------------+---------+------+---------+-------------+

On InnoDB, this isn't exactly blazing, but it's not bad, either.

This week I'm trying out MySQL 5.5.11, and was surprised to see that the same query is many times slower. With the cache primed, it takes around 90 seconds, compared to 5 seconds before. The plan now looks like this:

mysql> explain select count(distinct id) from x;
+----+-------------+----------+-------+---------------+---------+---------+------+---------+-------------------------------------+
| id | select_type | table    | type  | possible_keys | key     | key_len | ref  | rows    | Extra                               |
+----+-------------+----------+-------+---------------+---------+---------+------+---------+-------------------------------------+
|  1 | SIMPLE      | x        | range | NULL          | PRIMARY | 4       | NULL | 1234567 | Using index for group-by (scanning) |
+----+-------------+----------+-------+---------------+---------+---------+------+---------+-------------------------------------+

One way to make it go fast again is to use select count(id) from x, which is safe because id is a primary key, but I'm going through some abstraction layers (like NHibernate) that make this a non-trivial task.

I tried analyze table x but it didn't make any appreciable difference.

It looks kind of like this bug, though it's not clear what versions that applies to, or what's happening (nobody's touched it in a year yet it's "serious/high/high").

Is there any way, besides simply changing my query, to get MySQL to be smarter about this?

UPDATE:

As requested, here's a way to reproduce it, more or less. I wrote this SQL script to generate 1 million rows of dummy data (takes 10 or 15 minutes to run):

delimiter $$
drop table if exists x;
create table x (
  id integer unsigned not null auto_increment,
  a integer,
  b varchar(100),
  c decimal(9,2),
  primary key (id),
  index ix_a (a),
  index ix_b (b),
  index ix_c (c)
) engine=innodb;
drop procedure if exists fill;
create procedure fill()
begin
  declare i int default 0;
  while i < 1000000 do
    insert into x (a,b,c) values (1,"one",1.0);
    set i = i+1;
  end while;
end$$
delimiter ;
call fill();

When it's done, I observe this behavior:

• 5.1.48
  • select count(distinct id) from x
    • EXPLAIN is: key: ix_a, Extra: Using index
    • takes under 1.0 sec to run
  • select count(id) from x
    • EXPLAIN is: key: ix_a, Extra: Using index
    • takes under 0.5 sec to run
• 5.5.11
  • select count(distinct id) from x
    • EXPLAIN is: key: PRIMARY, Extra: Using index for group-by
    • takes over 7.0 sec to run
  • select count(id) from x
    • EXPLAIN is: key: ix_a, Extra: Using index
    • takes under 0.5 sec to run

EDIT:

If I modify the query in 5.5 by saying

select count(distinct id) from x force index (ix_a);

it runs much faster. Indexes b and c also work (to varying degrees), and even forcing index PRIMARY helps.

Answer 1

I'm not making any promises that this will be better but, as a possible work around, you could try:

SELECT COUNT(*)
    FROM (SELECT id
              FROM x
              GROUP BY id) t

Answer 2

I'm not sure why you need DISTINCT on a unique primary key. It looks like MySQL is viewing the DISTINCT keyword as an operator and losing the ability to make use of the index (as would any operation on a field.) Other SQL engines also sometimes don't optimize searches on expressions very well, so it's not a surprise.

---

I note your comment in another answer about this being an artifact of your ORM. Have you ever read the famous Leaky Abstractions blog by Joel Spolsky? I think you are there. Sometimes you end up spending more time straightening out the tool than you spend on the problem you're using the tool to solve.

Answer 3

I dont know if you have realiased, but counting the rows on a large database with InnoDB is slow, even without the distinct keyword. InnoDB does not cache the rowcount in the table metadata, MyISAM does.

I would suggest you do one of two things

1) create a trigger that inserts/updates distinct counts into another table on insertion.

2) slave another MySQL server to your database, but change the table type on the slave only, to MyISAM and perform your query there (this is probarbly overkill).

Answer 4

I may be missreading your question, but if id is the primary key of table x, then the following two queries are logically equivalent:

select count(distinct id) from x;

select count(*) from x;

...regardless of whether the optimizer realizes this. Distinct generally implies a sort or scanning the index in order, which is considerably slower than just counting the rows.

Answer 5

Creative use of autoincrement fields
Note that your id is autoincrement.
It will add +1 after each insert.

However it does not reuse numbers, so if you delete a row you need to track of that.
My idea goes something like this.

 Count(rows) = Max(id) - number of deletions - starting(id) + 1

Scenario using update

Create a separate table with the totals per table.

table counts 
  id integer autoincrement primary key
  tablename varchar(45)  /*not needed if you only need to count 1 table*/
  start_id integer default maxint
  delete_count 

Make sure you extract the starting_id before the first delete(!) into the table and do

INSERT INTO counts (tablename, start_id, delete_count)
  SELECT 'x', MIN(x.id), 0
  FROM x;

Now create a after delete trigger.

DELIMITER $$

CREATE TRIGGER ad_x_each AFTER DELETE ON x FOR EACH ROW
BEGIN
  UPDATE counts SET delete_count = delete_count + 1 WHERE tablename = 'x';
END $$

DELIMITER ;

IF you want to have the count, you do

SELECT max(x.id) - c.start_id + 1 - c.delete_count as number_of_rows
FROM x 
INNER JOIN counts c ON (c.tablename = 'x') 

This will give you your count instantly, with requiring a trigger to count on every insert.

insert scenario

If you have lots of deletes, you can speed up the proces by doing an insert instead of an update in the trigger and selecting

TABLE count_x  /*1 counting table per table to keep track of*/
  id integer autoincrement primary key /*make sure this field starts at 1*/
  start_id integer default maxint  /*do not put an index on this field!*/

Seed the starting id into the count table.

INSERT INTO counts (start_id) SELECT MIN(x.id) FROM x;

Now create a after delete trigger.

DELIMITER $$

CREATE TRIGGER ad_x_each AFTER DELETE ON x FOR EACH ROW
BEGIN
  INSERT INTO count_x (start_id) VALUES (default);     
END $$

DELIMITER ;

SELECT max(x.id) - min(c.start_id) + 1 - max(c.id) as number of rows
FROM x
JOIN count_x as c  ON (c.id > 0)

You'll have to test which approach works best for you.

Note that in the insert scenario you don't need delete_count, because you are using the autoincrementing id to keep track of the number of deletions.

Answer 6

select count(*)
from ( select distinct(id) from x)