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.
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.
