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.