List:Cluster« Previous MessageNext Message »
From:Jon Stephens Date:January 29 2010 6:08am
Subject:Re: GROUP BY does not use index on ndbcluster tables
View as plain text  
Casey B wrote:
> It appears that ndb tables will not use an index for GROUP BY operations,
> even if FORCE INDEX is used.
> 
> This results in temporary tables and sloooow queries.
> 
> Is this a known issue, bug or user error?

Sounds familiar... but also sounds like it could be a bug. Please file a 
bug report at http://bugs.mysql.com/, including a repeatable test case, 
and let's find out.

Please send the bug report number after you've filed it, so I can 
subscribe to it for updates.

Thanks!

jon.

> 
> 
> Example:
> 
> CREATE TABLE `tester` (
> `id` INT( 11 ) NOT NULL AUTO_INCREMENT ,
> `fk` INT( 11 ) NOT NULL ,
> `data` VARCHAR( 11 ) NOT NULL ,
> PRIMARY KEY ( `id` ) ,
> INDEX ( `fk` )
> ) ENGINE = NDBCLUSTER;
> 
> INSERT INTO `tester` (`id` ,`fk` ,`data`)
> VALUES (NULL , '1', 'test'), (NULL , '2', 'test'), (NULL , '1', 'test'),
> (NULL , '2', 'test'), (NULL , '3', 'test'), (NULL , '4', 'test'), (NULL ,
> '5', 'test'), (NULL , '6', 'test'), (NULL , '1', 'test'), (NULL , '1',
> 'test');
> 
> 
> mysql> explain extended SELECT DISTINCT fk FROM tester;
>
> +----+-------------+--------+------+---------------+------+---------+------+------+----------+-----------------+
> | id | select_type | table  | type | possible_keys | key  | key_len | ref  |
> rows | filtered | Extra           |
>
> +----+-------------+--------+------+---------------+------+---------+------+------+----------+-----------------+
> |  1 | SIMPLE      | tester | ALL  | NULL          | NULL | NULL    | NULL
> |   10 |    20.00 | Using temporary |
>
> +----+-------------+--------+------+---------------+------+---------+------+------+----------+-----------------+
> 1 row in set, 1 warning (0.00 sec)
> 
> mysql> explain extended SELECT DISTINCT fk FROM tester FORCE INDEX FOR GROUP
> BY (`fk`);
>
> +----+-------------+--------+-------+---------------+------+---------+------+------+----------+-------+
> | id | select_type | table  | type  | possible_keys | key  | key_len | ref
> | rows | filtered | Extra |
>
> +----+-------------+--------+-------+---------------+------+---------+------+------+----------+-------+
> |  1 | SIMPLE      | tester | index | NULL          | fk   | 4       | NULL
> |   10 |   100.00 |       |
>
> +----+-------------+--------+-------+---------------+------+---------+------+------+----------+-------+
> 1 row in set, 1 warning (0.02 sec)
> 
> 
> 
> Notice the missing "Using Index" from the Extra column.
> 


-- 


Jon Stephens - jon.stephens@stripped
Technical Writer
MySQL Documentation Team
Sun Microsystems AB
MySQL and Software Infrastructure Group
Liljeholmen (Stockholm), Sweden
Summer: UTC +02.00 / Winter: UTC +01.00
Mobile: +46 (0) 736 773 993
Skype: plastic-fish
MySQL: www.mysql.com
Sun: www.sun.com

Thread
GROUP BY does not use index on ndbcluster tablesCasey B28 Jan
  • Re: GROUP BY does not use index on ndbcluster tablesJon Stephens29 Jan
    • Re: GROUP BY does not use index on ndbcluster tablesCasey B29 Jan