Optimizing query on huge tables
Hi, i have the following table which is the main table that has historic records from six months and has 14 million of records aprox.
CREATE TABLE `imeiTracking` (
`servedMsisdn` varchar(15) NOT NULL,
`servedImsi` varchar(16) NOT NULL,
`servedImei` varchar(16) NOT NULL,
`qt_moc` mediumint(8) unsigned DEFAULT NULL,
`qt_mtc` mediumint(8) unsigned DEFAULT NULL,
`qt_smsmo` mediumint(8) unsigned DEFAULT NULL,
`qt_smsmt` mediumint(8) unsigned DEFAULT NULL,
`cid` varchar(5) DEFAULT NULL,
`lac` varchar(5) DEFAULT NULL,
`db_date` date NOT NULL DEFAULT '0000-00-00',
`lastEvent_date` date NOT NULL DEFAULT '0000-00-00',
PRIMARY KEY (`servedMsisdn`,`servedImsi`,`servedImei`),
KEY `ix_tracking_msisdn` (`servedMsisdn`),
KEY `ix_tracking_imsi` (`servedImsi`),
KEY `ix_tracking_imei` (`servedImei`),
KEY `ix_tracking_cid` (`cid`),
KEY `ix_tracking_lac` (`lac`)
) ENGINE=MyISAM
Daily, on that table i execute the following query which has the records for the previus day and has aproximately 1.2 millions of records, and its created daily.
CREATE TABLE `voice_imeiTracking_intermediate` (
`servedMsisdn` varchar(15) NOT NULL,
`servedImsi` varchar(16) NOT NULL,
`servedImei` varchar(16) NOT NULL,
`qt_moc` mediumint(8) unsigned DEFAULT NULL,
`qt_mtc` mediumint(8) unsigned DEFAULT NULL,
`qt_smsmo` mediumint(8) unsigned DEFAULT NULL,
`qt_smsmt` mediumint(8) unsigned DEFAULT NULL,
`cid` varchar(5) DEFAULT NULL,
`lac` varchar(5) DEFAULT NULL,
`db_date` date NOT NULL DEFAULT '0000-00-00',
`lastEvent_date` date NOT NULL DEFAULT '0000-00-00',
KEY `ix_tracking_full` (`servedMsisdn`,`servedImsi`,`servedImei`),
KEY `ix_tracking_cid` (`cid`),
KEY `ix_tracking_lac` (`lac`)
) ENGINE=MyISAM
The query that updates the main table from the intermediate table is:
INSERT INTO midasReports.imeiTracking (servedMsisdn, servedImsi, servedImei, qt_moc, qt_mtc, qt_smsmo, qt_smsmt, cid, lac, db_date, lastEvent_date)
SELECT servedmsisdn, servedimsi, servedimei, qt_moc, qt_mtc, qt_smsmo, qt_smsmt, cid, lac, db_date, lastEvent_date FROM midasReports.voice_imeiTracking_intermediate
ON DUPLICATE KEY UPDATE
imeiTracking.qt_moc=imeiTracking.qt_moc+VALUES(qt_moc),
imeiTracking.qt_mtc=imeiTracking.qt_mtc+VALUES(qt_mtc),
imeiTracking.qt_smsmo=imeiTracking.qt_smsmo+VALUES(qt_smsmo),
imeiTracking.qt_smsmt=imeiTracking.qt_smsmt+VALUES(qt_smsmt),
imeiTracking.cid=VALUES(cid),
imeiTracking.lac=VALUES(lac),
imeiTracking.lastEvent_date=VALUES(lastEvent_date);
The issue? This query takes way too long to execute and it lowers the performance of my server. Notice that both tables have the same index (the primary key on the main table), and there are indexes on almost every column.
How can i improve the performance of this query, or at least decrease the impacts it has on my server?
Thank you in advance,
Fernando