Announcement

Announcement Module
Collapse
No announcement yet.

GROUP BY causes incorrect index selection in 5.1.23(24)

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • GROUP BY causes incorrect index selection in 5.1.23(24)

    Is it only me or others have experienced this as well? :
    After upgrading to 5.1.23 from 22 I noticed that the select performance has degraded, so I did the Explain and found out that in 5.1.23/24 the GROUP BY causes incorrect index selection - works fine in 5.1.22 and before. Did something change how the indexes are chosen on 5.1.23?
    Any help is greatly appreciated!
    Thx,
    dstv

    here is the table definition:
    CREATE TABLE `pro_attribute` (
    `ObjectId` decimal(18,0) NOT NULL,
    `ClassAttrName` varchar(255) NOT NULL,
    `Data` varchar(2000) DEFAULT NULL,
    `VersionId` int(10) DEFAULT NULL,
    `Timestamp` char(25) DEFAULT NULL,
    `Synchronized` tinyint(1) DEFAULT NULL,
    `Locked` tinyint(1) DEFAULT NULL,
    PRIMARY KEY (`ObjectId`,`ClassAttrName`),
    KEY `Index_Name_Data` (`ClassAttrName`,`Data`(255)) USING BTREE,
    CONSTRAINT `FK_pro_attribute_objectid` FOREIGN KEY (`ObjectId`) REFERENCES `pro_object` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 PACK_KEYS=1

    and here are the explains:

    5.1.22 (works fine):

    EXPLAIN EXTENDED SELECT ObjectId, COUNT(ObjectId) CNT FROM PRO_ATTRIBUTE WHERE (ClassAttrName='OBJECT_ParentId' AND Data = '184' ) OR (ClassAttrName='OBJECT_ClassName' AND Data IN ('Activity', 'ExternalActivity', 'InternalActivity')) GROUP BY ObjectId HAVING CNT = 2;

    +----+-------------+---------------+-------+---------------- -+-----------------+---------+------+------+----------+----- -----------------------------------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    +----+-------------+---------------+-------+---------------- -+-----------------+---------+------+------+----------+----- -----------------------------------------+
    | 1 | SIMPLE | PRO_ATTRIBUTE | range | Index_Name_Data | Index_Name_Data | 1535 | NULL | 14 | 100.00 | Using where; Using temporary; Using filesort |
    +----+-------------+---------------+-------+---------------- -+-----------------+---------+------+------+----------+----- -----------------------------------------+
    1 row in set, 1 warning (0.53 sec)

    ================================================== ===
    5.1.23/24 (wrong key selected):

    EXPLAIN EXTENDED SELECT ObjectId, COUNT(ObjectId) CNT FROM PRO_ATTRIBUTE WHERE (ClassAttrName='OBJECT_ParentId' AND Data = '184' ) OR (ClassAttrName='OBJECT_ClassName' AND Data IN ('Activity', 'ExternalActivity', 'InternalActivity')) GROUP BY ObjectId HAVING CNT = 2;

    +----+-------------+---------------+-------+---------------- -+---------+---------+------+---------+----------+---------- ---+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    +----+-------------+---------------+-------+---------------- -+---------+---------+------+---------+----------+---------- ---+
    | 1 | SIMPLE | PRO_ATTRIBUTE | index | Index_Name_Data | PRIMARY | 775 | NULL | 1291738 | 0.00 | Using where |
    +----+-------------+---------------+-------+---------------- -+---------+---------+------+---------+----------+---------- ---+
    1 row in set, 1 warning (0.00 sec)

    If I take "GROUP BY" out the explain show the same result as for 5.1.22.
Working...
X