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
