List:General Discussion« Previous MessageNext Message »
From:Magnus Sj√§lander Date:February 10 2003 6:58pm
Subject:Table performance, indexing?
View as plain text  
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

Thread
How to Archive and restore the data in MySQL database.Prasad Budim Ram7 Feb
  • Re: How to Archive and restore the data in MySQL database.Dan Nelson8 Feb
  • Re: How to Archive and restore the data in MySQL database.olinux8 Feb
  • RE: How to Archive and restore the data in MySQL database.B. van Ouwerkerk8 Feb
    • Table performance, indexing?Magnus Sj√§lander10 Feb
  • Re: How to Archive and restore the data in MySQL database.Stefan Hinz8 Feb
Re: How to Archive and restore the data in MySQL database.mysql-list-bot8 Feb