Problem part solved. I have added the index "Mine" and the query runs for 20 secs on many of my large groups of data. The index obviously helps a lot, but I think the left join idea might have to be investigated. I would need some help constructing such a thing.
I have only a very basic understanding of indexes. Is the index "fx_QSO_User_idx" adding any value? Will the system be able to exploit the clientID field in the other two indexes?
CREATE TABLE IF NOT EXISTS `QSO` (
`clientID` int(11) NOT NULL COMMENT 'ACMA client ID of user',
`STATION_CALLSIGN` varchar(15) NOT NULL COMMENT 'Callsign used when making QSO',
`CALLSIGN` varchar(15) NOT NULL COMMENT 'Station contacted in QSO',
`QSO_DATE` date NOT NULL COMMENT 'Date of QSO',
`TIME_ON` time NOT NULL COMMENT 'Start time of QSO',
`BAND` enum('2190m','560m','160m','80m','60m','40m','30m','20m','17m','15m','12m','10m','6m','4m','2m','1.25m','70cm','33cm','23cm','13cm','9cm','6cm','3cm','1.25cm','6mm','4mm','2.5mm','2mm','1mm') NOT NULL COMMENT 'Band of QSO',
`MODE` enum('AM','AMTORFEC','ASCI','ATV','CHIP64','CHIP128','CLO','CONTESTI','CW','DSTAR','DOMINO','DOMINOF','FAX','FM','FMHELL','FSK31','FSK441','GTOR','HELL','HELL80','HFSK','ISCAT','JT44','JT4A','JT4B','JT4C','JT4D','JT4E','JT4F','JT4G','JT65','JT65A','JT65B','JT65C','JT9','JT6M','MFSK8','MFSK16','MT63','OLIVIA','OPERA','PAC','PAC2','PAC3','PAX','PAX2','PCW','PKT','PSK10','PSK2K','PSK31','PSK63','PSK63F','PSK125','PSKAM10','PSKAM31','PSKAM50','PSKFEC31','PSKHELL','Q15','QPSK31','QPSK63','QPSK125','ROS','RTTY','RTTYM','SSB','SSTV','THRB','THOR','THRBX','TOR','V4','VOI','WINMOR','WSPR') NOT NULL COMMENT 'Mode of QSO',
`DXCC` smallint(6) NOT NULL COMMENT 'DXCC entity number',
`RST_SENT` char(4) DEFAULT NULL COMMENT 'Report sent',
`GRIDSQUARE` char(4) DEFAULT NULL COMMENT 'Maidenhead locator',
`FLAGS` set('QSL_RCVD','EQSL_QSL_RCVD','LOTW_QSL_RCVD','QSL_OK','EQSL_OK','LOTW_OK','QRP','SWL','DXCC_OK') DEFAULT NULL COMMENT 'Discrete flags',
`Checker1` int(11) DEFAULT NULL COMMENT 'clientID of card checker #1',
`Checker2` int(11) DEFAULT NULL COMMENT 'clientID of card checker #2',
PRIMARY KEY (`clientID`,`CALLSIGN`,`QSO_DATE`,`TIME_ON`,`BAND`,`MODE`),
KEY `fk_QSO_User_idx` (`clientID`),
KEY `Mine` (`clientID`,`BAND`,`MODE`,`DXCC`)
) ENGINE=MyISAM DEFAULT CHARSET=ascii COMMENT='Details of all QSO';
Edited 1 time(s). Last edit at 02/13/2014 07:00AM by Marc Hillman.