It seems that it takes an awful long time (almost 2 seconds with 93658 rows with the Select listed below) to return data from a table in my database. Unfortunately, the basic table design is stuck how it is because of the way an external software package is set up. I can't do anything to change the way it Inserts or Selects data from the table. The most I can do is to change the index/indices and the engine used and possibly data types if it would help with anything.
The problem is, I have to write to this database every second. And every time a chart opens, I have to read the data, back a certain period of time determined by the user opening the chart.
All of these is extremely CPU intensive-I would like to cut down a bit on the amount of processing by tweaking my settings, but feel a bit uncomfortable doing much of this without having some suggestions from the more experienced.
I would prefer to leave MySQL on a machine shared with other software because the machine is in a very remote location, plaqued by storms, and power outages, mounted in a weatherproof enclosure with climate control, 15 ft in the air on a communications tower. Its a Windows box, Intel Core 2 Quad Q6600 Kentsfield 2.4GHz with 4 GB Ram and 1TB RAID.
Any input is appreciated
Here is the insert that the software uses:
INSERT INTO TREND001 (Time_Stamp, Time_Stamp_ms, SK00_SC_MODA_CH3, SK00_SC_MODA_CH4, SK00_LP_Rate, SK00_FL_RateAll, SK00_FL_RateOil, SK00_FL_RateWater, SK00_HP_Rate, SK00_SC_MODA_CH1, Bias ) VALUES (_latin1'2009-06-07 10:03:42', 193, 5.00015258789062500e+001, 5.00015258789062500e+001, 1.61515501325249430e+000, 8.36653643300294370e+000, 2.50996092990088350e-001, 8.11554034001285630e+000, 6.07288194806405550e+000, 8.99993896484375000e+002, 300 )
And the select it uses to read the data
SELECT Time_Stamp, Time_Stamp_ms, SK00_SC_MODA_CH3, SK00_SC_MODA_CH4, SK00_LP_Rate, SK00_FL_RateAll, SK00_FL_RateOil, SK00_FL_RateWater, SK00_HP_Rate, SK00_SC_MODA_CH1, Bias FROM TREND001 WHERE ( ( Time_Stamp > _latin1'2009-06-06 22:59:00' OR ( Time_Stamp = _latin1'2009-06-06 22:59:00' AND Time_Stamp_ms >= 100)) AND ( Time_Stamp < _latin1'2009-06-07 09:50:47' OR ( Time_Stamp = _latin1'2009-06-07 09:50:47' AND Time_Stamp_ms <= 634)) AND Bias = 300 ) OR ( ( Time_Stamp > _latin1'2009-06-06 21:59:00' OR ( Time_Stamp = _latin1'2009-06-06 21:59:00' AND Time_Stamp_ms >= 100)) AND ( Time_Stamp < _latin1'2009-06-07 08:50:47' OR ( Time_Stamp = _latin1'2009-06-07 08:50:47' AND Time_Stamp_ms <= 634)) AND Bias = 360 ) ORDER BY Time_Stamp ASC, Time_Stamp_ms ASC
DROP TABLE IF EXISTS `clscada`.`trend001`;
CREATE TABLE `clscada`.`trend001` (
`Time_Stamp` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`Time_Stamp_ms` int(11) NOT NULL DEFAULT '0',
`SK00_SC_MODA_CH3` double DEFAULT NULL,
`SK00_SC_MODA_CH4` double DEFAULT NULL,
`SK00_LP_Rate` double DEFAULT NULL,
`SK00_FL_RateAll` double DEFAULT NULL,
`SK00_FL_RateOil` double DEFAULT NULL,
`SK00_FL_RateWater` double DEFAULT NULL,
`SK00_HP_Rate` double DEFAULT NULL,
`SK00_SC_MODA_CH1` double DEFAULT NULL,
`Bias` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`Time_Stamp`,`Time_Stamp_ms`,`Bias`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci PACK_KEYS=1;
Edited 1 time(s). Last edit at 06/07/2009 09:27AM by Justin Wood.