Optimize complex query with arithmetic calculations
Hi, I am newbie in MySQL and db operation in general, and I am trying to optimize time-consuming querys made to the same table periodically. This is the structure of the table (MyISAM):
+-----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------------------+------+-----+---------+----------------+
| id | int(10) unsigned | | PRI | NULL | auto_increment |
| ip_orig | char(15) | YES | | NULL | |
| ip_dest | char(15) | YES | | NULL | |
| port_orig | smallint(5) unsigned | YES | | NULL | |
| port_dest | smallint(5) unsigned | YES | | NULL | |
| fechahora | datetime | YES | | NULL | |
| enviados | int(10) unsigned | YES | | NULL | |
| recibidos | int(10) unsigned | YES | | NULL | |
| duracion | int(10) unsigned | YES | | NULL | |
| politica | tinyint(3) unsigned | YES | | NULL | |
| sentido | enum('E','S') | YES | | NULL | |
+-----------+----------------------+------+-----+---------+----------------+
And this is a typical query to this database:
select sum(enviados*(least(120, unix_timestamp(date_add(fechahora,interval duracion second)) - unix_timestamp('2005-05-22 19:52:00')) / (duracion*120))) as envi, sum(recibidos*(least(120, unix_timestamp(date_add(fechahora,interval duracion second)) - unix_timestamp('2005-05-22 19:52:00')) / (duracion*120))) as reci, sum((enviados+recibidos)*(least(120, unix_timestamp(date_add(fechahora,interval duracion second)) - unix_timestamp('2005-05-22 19:52:00')) / (duracion*120))) as sumai from trafico2 where ( fechahora <'2005-05-22 19:52:00' and date_add(fechahora,interval duracion second)>='2005-05-22 19:52:00' and sentido='S');
These querys are normally used once with the same time interval, and performed for retrieving graphical statistics at night, so I am not interested in accumulate the calculations in a separated table as they are produced, as the server is quite charged during the day.
The thing is it needs about 3 hours to generate all the needed statistics for a whole day, and that's a lot. Possible optimizations I have thought:
- Replicate the data in a heap table, and use it to perform the daily calculations
- Use timestamp instead of datetime
Also, this table is susceptible to be queried later using similar querys but grouped by ip_orig or ip_dest, and variants, so the heap type table will not help here.
I welcome any suggestions. Many thanks in advance.