MySQL Forums
Forum List  »  Performance

Poor performance with an "OR"
Posted by: Xavier Gosset
Date: May 11, 2010 02:16AM

Hello,
I have a problem with this query:
SELECT COUNT(*) FROM operations o WHERE SourceResourceId = 'abcd' OR DestinationResourceId = 'abcd';

This query is taking more than 1 minute.
The table contains more than 8000000 rows.

Here is the structure of the table:
DROP TABLE IF EXISTS `traceability`.`operations`;
CREATE TABLE `traceability`.`operations` (
`OperationId` varchar(40) character set utf8 NOT NULL,
`StartTime` datetime NOT NULL,
`StopTime` datetime NOT NULL,
`SourceResourceId` varchar(30) character set utf8 NOT NULL,
`DestinationResourceId` varchar(30) character set utf8 NOT NULL,
`OperationTypeId` varchar(30) character set utf8 NOT NULL,
PRIMARY KEY (`OperationId`),
KEY `Index_StartTime` (`StartTime`),
KEY `Index_StopTime` (`StopTime`),
KEY `Index_OperationTypeId` (`OperationTypeId`),
KEY `Index_SourceResourceId` USING BTREE (`SourceResourceId`),
KEY `Index_DestinationResourceId` USING BTREE (`DestinationResourceId`),
KEY `Index_SourceDestination` (`SourceResourceId`,`DestinationResourceId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 6010880 kB';

Do you have an idea?

Options: ReplyQuote


Subject
Views
Written By
Posted
Poor performance with an "OR"
3110
May 11, 2010 02:16AM
1137
May 12, 2010 08:45AM
1106
May 14, 2010 06:39PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.