Databases
StackOverflow2013
Postsdbo
POMySQL 5.5 "select distinct" is really slow
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL 5.5 "select distinct" is really slow
    primarykey
    data
    text
    <p>One of the things my app does a fair amount is:</p> <pre><code>select count(distinct id) from x; </code></pre> <p>with <code>id</code> the primary key for table <code>x</code>. With MySQL 5.1 (and 5.0), it looks like this:</p> <pre><code>mysql&gt; 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 | +----+-------------+----------+-------+---------------+-----------------+---------+------+---------+-------------+ </code></pre> <p>On InnoDB, this isn't exactly blazing, but it's not bad, either.</p> <p>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:</p> <pre><code>mysql&gt; 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) | +----+-------------+----------+-------+---------------+---------+---------+------+---------+-------------------------------------+ </code></pre> <p>One way to make it go fast again is to use <code>select count(id) from x</code>, which is safe because <code>id</code> is a primary key, but I'm going through some abstraction layers (like NHibernate) that make this a non-trivial task.</p> <p>I tried <code>analyze table x</code> but it didn't make any appreciable difference.</p> <p>It looks kind of like <a href="http://bugs.mysql.com/bug.php?id=49111" rel="nofollow">this bug</a>, 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").</p> <p>Is there any way, besides simply changing my query, to get MySQL to be smarter about this?</p> <p><strong>UPDATE:</strong></p> <p>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):</p> <pre><code>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 &lt; 1000000 do insert into x (a,b,c) values (1,"one",1.0); set i = i+1; end while; end$$ delimiter ; call fill(); </code></pre> <p>When it's done, I observe this behavior:</p> <ul> <li>5.1.48 <ul> <li><code>select count(distinct id) from x</code> <ul> <li>EXPLAIN is: key: ix_a, Extra: Using index</li> <li>takes under 1.0 sec to run</li> </ul></li> <li><code>select count(id) from x</code> <ul> <li>EXPLAIN is: key: ix_a, Extra: Using index</li> <li>takes under 0.5 sec to run</li> </ul></li> </ul></li> <li>5.5.11 <ul> <li><code>select count(distinct id) from x</code> <ul> <li>EXPLAIN is: key: PRIMARY, Extra: Using index for group-by</li> <li>takes over 7.0 sec to run</li> </ul></li> <li><code>select count(id) from x</code> <ul> <li>EXPLAIN is: key: ix_a, Extra: Using index</li> <li>takes under 0.5 sec to run</li> </ul></li> </ul></li> </ul> <p><strong>EDIT:</strong></p> <p>If I modify the query in 5.5 by saying</p> <pre><code>select count(distinct id) from x force index (ix_a); </code></pre> <p>it runs much faster. Indexes b and c also work (to varying degrees), and even forcing index <code>PRIMARY</code> helps.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USKen
    1. USKen
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMySQL 5.5 "select distinct" is really slow
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POMySQL 5.5 "select distinct" is really slow
      1. USJohan
      1. VTFavorite
    3. VO
      singulars
      1. POMySQL 5.5 "select distinct" is really slow
      1. This table or related slice is empty.
      1. VTUpMod
    1. COAre the two x tables exactly the same? Please run `SHOW CREATE TABLE x\G` on both DBs and post the results. I'm particularly interested in the ix_blahblahblah index which was used in 5.1 but not 5.5.
      singulars
      1. POMySQL 5.5 "select distinct" is really slow
      1. USIke Walker
    2. COIke: I don't want to post the actual code I'm working on, but I'll try to come up with something similar that exhibits this problem.
      singulars
      1. POMySQL 5.5 "select distinct" is really slow
      1. USKen
    3. COIke: Simplified example added! The table is a bit smaller (fewer rows and fewer columns) than my actual data, but the performance drop in 5.5 is still almost 10x.
      singulars
      1. POMySQL 5.5 "select distinct" is really slow
      1. USKen
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload