Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

* Update *

This bug has been fixed in 5.5.27, 5.6.7 and 5.7.0


I feel like I've found a bug in mysql, but haven't been able to track it down. Any idea what's going on here?

This was working just find in mysql 5.1, but then it never used the INDEX FOR GROUP BY.

mysql>  select count(DISTINCT(IF(user_id = 0, ip, user_id))) from tm_tmp group by artist_id limit 11;
+-----------------------------------------------+
| count(DISTINCT(IF(user_id = 0, ip, user_id))) |
+-----------------------------------------------+
|                                             1 |
|                                             2 |
|                                             1 |
|                                             2 |
|                                             1 |
|                                             4 |
|                                             1 |
|                                             3 |
|                                             2 |
|                                             1 |
|                                             1 |
+-----------------------------------------------+
11 rows in set (0.00 sec)

mysql>  select count(DISTINCT(IF(user_id = 0, ip, user_id))) from tm_tmp group by artist_id limit 12;
+-----------------------------------------------+
| count(DISTINCT(IF(user_id = 0, ip, user_id))) |
+-----------------------------------------------+
|                                             0 |
|                                             0 |
|                                             0 |
|                                             0 |
|                                             0 |
|                                             0 |
|                                             0 |
|                                             0 |
|                                             0 |
|                                             0 |
|                                             0 |
|                                             0 |
+-----------------------------------------------+
12 rows in set (0.00 sec)


mysql> explain extended  select          count(DISTINCT(IF(user_id = 0, ip, user_id))) from tm_tmp group by artist_id limit 11;
+----+-------------+--------+-------+---------------+-----------------+---------+------+------+----------+-------------+
| id | select_type | table  | type  | possible_keys | key             | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+-------+---------------+-----------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tm_tmp | index | NULL          | sp_artist_reach | 12      | NULL |   45 |    26.67 | Using index |
+----+-------------+--------+-------+---------------+-----------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain extended  select          count(DISTINCT(IF(user_id = 0, ip, user_id))) from tm_tmp group by artist_id limit 12;
+----+-------------+--------+-------+---------------+-----------------+---------+------+------+----------+--------------------------+
| id | select_type | table  | type  | possible_keys | key             | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+--------+-------+---------------+-----------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | tm_tmp | range | NULL          | sp_artist_reach | 4       | NULL |   12 |   100.00 | Using index for group-by |
+----+-------------+--------+-------+---------------+-----------------+---------+------+------+----------+--------------------------+

mysql> show create table tm_tmp;
| Table  | Create Table
| tm_tmp | CREATE TABLE `tm_tmp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `artist_id` int(11) NOT NULL DEFAULT '0',
  `user_id` int(11) NOT NULL DEFAULT '0',
  `ip` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `sp_artist_reach` (`artist_id`,`user_id`,`ip`),
) ENGINE=MyISAM AUTO_INCREMENT=46 DEFAULT CHARSET=latin1 |
1 row in set (0.00 sec)

* Edit *

I have filed the following bug report: http://bugs.mysql.com/64445. We'll see what comes out of it.

The solution we were able to get working was forcing a derived table like this:

SELECT count(DISTINCT(IF(user_id = 0, ip, user_id)))
FROM
(
 SELECT artist_id, user_id, ip
 FROM tm_tmp
 GROUP BY artist_id, user_id, ip
) summary
GROUP BY artist_id
LIMIT 12;

mysql> explain SELECT count(DISTINCT(IF(user_id = 0, ip, user_id))) FROM (  SELECT
artist_id, user_id, ip  FROM tm_tmp  GROUP BY artist_id, user_id, ip ) summary GROUP BY artist_id LIMIT 11;
+----+-------------+------------+-------+---------------+-----------------+---------+------+------+--------------------------+
| id | select_type | table      | type  | possible_keys | key             | key_len |  ref | rows | Extra                    |
+----+-------------+------------+-------+---------------+-----------------+---------+------+------+--------------------------+
|  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL            | NULL    | NULL |   21 | Using filesort           |
|  2 | DERIVED     | tm_tmp     | range | NULL          | sp_artist_reach | 12      | NULL |   23 | Using index for group-by |
+----+-------------+------------+-------+---------------+-----------------+---------+------+------+--------------------------+
share|improve this question
    
I am on 5.1.47. Plus, the limit isn't preventing the index for group by. The index for group by is what's causing the error. –  trimbletodd Feb 23 '12 at 21:56
    
Apologies, misread what's happening :) –  Derek Downey Feb 23 '12 at 22:00
    
Looks like we've found a workaround by forcing a derived table. Seems to solve the issue, at the expense of the query running 6x as long. The other way that worked was DISABLE KEYS, which isn't really much of a solution. –  trimbletodd Feb 23 '12 at 22:21
    
you can use a force index hint for the optimizer that will make this query to return ( run explain plan with order by only to find which index to use). –  user21303 Mar 14 '13 at 18:38
    
Have you tried if rewritting the query with CASE makes any difference? count(DISTINCT CASE WHEN user_id = 0 THEN ip ELSE user_id END) –  ypercube Mar 15 '13 at 7:46
add comment

3 Answers

up vote 4 down vote accepted

I would definitely submit this as a bug. There might even be two bugs here.

First, there was a change in MySQL 5.5 that allowed COUNT(DISTINCT) to use a loose index scan (indicated by the using index for group by in the explain). Documented change is at the bottom of this doc page. It looks like the structure of your query should not be eligible for a loose index:

Loose index scan is not applicable for the following queries:

SELECT COUNT(DISTINCT c1) FROM t1 GROUP BY c1;

Your use-case of wrapping the DISTINCT around an IF seems to be edge-case they could have missed.

Second, the output on the query that uses a loose index scan seems unexpected as well. I could see it being different because the loose index scan isn't retrieving all rows. But I would still expect it to be something other than 0 for all rows.

One thing I love about MySQL is when they open up their WorkLogs to the public. Here is the WorkLog on extending loose index scans to other aggregate functions (such as COUNT(DISTINCT) in your case).

share|improve this answer
    
Heck, even a third bug with GROUP BY with LIMIT 11 not using it, but LIMIT 12 does! –  Derek Downey Feb 23 '12 at 22:29
    
Strange behavior. I'll definitely submit it and put a link up in the post. Thanks for the help. –  trimbletodd Feb 23 '12 at 22:29
    
The reason I used 11 and 12 was that was the breaking point where the index changed. But it pretty succinctly demonstrated the problem! –  trimbletodd Feb 23 '12 at 22:31
    
Might have a look through the comments on this bug as well: bugs.mysql.com/bug.php?id=49111 –  Derek Downey Feb 23 '12 at 22:38
    
@DTest Nice research on this one. I added the question to my favorites. +1 !!! –  RolandoMySQLDBA Feb 23 '12 at 23:00
show 1 more comment

As well as Dtest's answers, I'd have suggested that LIMIT without ORDER BY is meaningless.

Well, this would be the case in SQL Server and others, but MySQL GROUP BY usually implies ORDER BY. It could be you are getting arbitrary rows for LIMIT 12 but implied sort with LIMIT 11.

So, try adding ORDER BY...

share|improve this answer
    
I gave it a shot, and had no luck unfortunately. Same results. –  trimbletodd Feb 24 '12 at 14:53
add comment

Besides the bugfix (congratulations for finding a bug by the way!), here is a suggestion for rewriting the query, that I think will be more efficient with an index on (artist_id, user_id, ip):

First limit the artist_id values to 11 (or 12 or whatever), then join the table and only then group by:

SELECT
    t.artist_id,
    COUNT(DISTINCT IF(t.user_id = 0, t.ip, t.user_id)) AS cnt 
FROM 
        ( SELECT DISTINCT artist_id 
          FROM tm_tmp 
          ORDER BY artist_id 
            LIMIT 12
        ) AS d
    JOIN
        tm_tmp AS t
            ON t.artist_id = d.artist_id
GROUP BY
    t.artist_id ;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.