MySQL Forums :: Performance :: Poor performance with an "OR"


Advanced Search

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" 2181 Xavier Gosset 05/11/2010 02:16AM
Re: Poor performance with an "OR" 908 Rick James 05/12/2010 08:45AM
Re: Poor performance with an "OR" 942 Xavier Gosset 05/14/2010 01:24AM
Re: Poor performance with an "OR" 884 Rick James 05/14/2010 06:39PM
Re: Poor performance with an "OR" 880 Xavier Gosset 05/15/2010 12:26PM


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.