Hello I have a performance problem when working against a large table that I hope that I can get some help with. The table stores data that is collected from a monitoring unit for a heating system for a larger house complex. The table is constructed as shown below.
Transceiver INT NOT NULL, Channel INT NOT NULL, Time DATETIME NOT NULL, Value FLOAT NOT NULL, Type INT NOT NULL Transceiver - is an id for each monitoring unit, today there are 7 of them Channel - The different kinds of values that is monitored, up to 30 for each Transceiver Time - The time when the value was measured Type - Indicates the time interval of the Value, every minute, average for the hour, day or month Types: 1 - every minute 2 - average hour 3 - average day 4 - average month At the moment there exist 10 millions rows in the table and they are distributed as follows: Type 1 - 9.7 M Type 2 - 0.3 M Type 3 - 0.04 M Type 4 - 0.0002 M 200+k rows are added for each day. When using an index on the time column to improve select queries of the following kind select * from data where transceiver=1 and channel=10101 type='X' ORDER BY time LIMIT 10 the time to retrieve the result for type 1 and 2 is improved and take around 0.1 second but for type 3 and 4 it takes several minutes to retrieve the result which for me is very strange. To improve the performance of the index the index buffer has been increased but other then that I'm using default values from the installation of the 3.23.52-nt windows version of the server. What I would like some opinions about is how to best organize the data to get the best performance. Should I use an index or should I organize the data in a different way to get the best performance for inserts and queries of the kind mentioned above. o Time-index - this gives strange performance results for type 3 and 4 o Time/Type-index - this doesn't give a reasonable performance increase compared to the increased time to insert rows o One table for each Type - This maybe gives a performance increase but is an impractical way to organize the data If I'm not capable of improving the overall performance I will have to use 2 tables where I have one where I store most of the data and one where I only have the most recently stored for quick accesses but this doesn't feel like a professional way to solve it. I would appreciate any help and opinions on the matter on how to best improve the performance. Thanks /Magnus --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php