Optimising query with composite index, which says using where

[Timothy Mifsud]

Hi,

I have the following table "endgames" as follows:

DROP TABLE IF EXISTS `DB`.`endgames`;
CREATE TABLE `DB`.`endgames` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`GamePosition` char(64) NOT NULL DEFAULT '0123421055555555CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCBBBBBBBB6789A876',
`GameIntroduction` text,
`Event` varchar(40) DEFAULT NULL,
`Site` varchar(40) DEFAULT NULL,
`GameDate` varchar(25) DEFAULT NULL,
`Round` varchar(10) DEFAULT NULL,
`WhitePlayer` varchar(80) NOT NULL,
`BlackPlayer` varchar(80) DEFAULT NULL,
`Result` tinyint(3) unsigned DEFAULT NULL,
`ECOCode` smallint(6) unsigned DEFAULT NULL,
`Annotator` varchar(100) DEFAULT NULL,
`EventCountry` varchar(40) DEFAULT NULL,
`GameText` text,
`WhiteMove` bit(1) DEFAULT b'1',
`MoveOffset` smallint(6) DEFAULT '0',
`TextComments` int(10) unsigned DEFAULT NULL,
`VariationCount` int(10) unsigned DEFAULT NULL,
`WhiteQueenCount` tinyint(3) unsigned NOT NULL,
`WhiteRookCount` tinyint(3) unsigned NOT NULL,
`WhiteBishopCount` tinyint(3) unsigned NOT NULL,
`WhiteKnightCount` tinyint(3) unsigned NOT NULL,
`WhitePawnCount` tinyint(3) unsigned NOT NULL,
`BlackQueenCount` tinyint(3) unsigned NOT NULL,
`BlackRookCount` tinyint(3) unsigned NOT NULL,
`BlackBishopCount` tinyint(3) unsigned NOT NULL,
`BlackKnightCount` tinyint(3) unsigned NOT NULL,
`BlackPawnCount` tinyint(3) unsigned NOT NULL,
PRIMARY KEY (`ID`),
KEY `IX_PieceCounts` (`WhiteQueenCount`,`WhiteRookCount`,`WhiteBishopCount`,`WhiteKnightCount`,`WhitePawnCount`,`BlackQueenCount`,`BlackRookCount`,`BlackBishopCount`,`BlackKnightCount`,`BlackPawnCount`)
) ENGINE=MyISAM AUTO_INCREMENT=58796 DEFAULT CHARSET=latin1;

As can be seen, there is a composite index on the Count fields (the fields I search on).

I am using the query below:

EXPLAIN

SELECT * FROM endgames WHERE WhiteQueenCount >=0 AND WhiteRookCount >=3 AND WhiteBishopCount >=0 AND WhiteKnightCount >=0 AND WhitePawnCount >=0 AND BlackQueenCount >=0 AND BlackRookCount >=0 AND BlackBishopCount >=0 AND BlackKnightCount >=0 AND BlackPawnCount >=0

UNION

SELECT * FROM endgames WHERE WhiteQueenCount >=0 AND WhiteRookCount >=0 AND WhiteBishopCount >=0 AND WhiteKnightCount >=0 AND WhitePawnCount >=0 AND BlackQueenCount >=0 AND BlackRookCount >=3 AND BlackBishopCount >=0 AND BlackKnightCount >=0 AND BlackPawnCount >=0

The output of that explain is the following:

1, 'PRIMARY', 'endgames', 'range', 'IX_PieceCounts', 'IX_PieceCounts', '10', '', 8421, 'Using where'
2, 'UNION', 'endgames', 'ALL', 'IX_PieceCounts', '', '', '', 58795, 'Using where'
, 'UNION RESULT', '<union1,2>', 'ALL', '', '', '', '', , ''

This may take up to 3 seconds in the live environment on a table with 60,000 rows. Is there any way I can optimise the query? In particular I am noticing that the IX_PieceCounts index is not being used in the second part after the UNION and I see "using where" and a large number of rows having to be examined.

Thanks in advance,
Tim