[linux] sql optimalisatie?

[Folkert van Heusden]

Iemand nog 'n ideetje hoe ik deze query kan versnellen?

SELECT heatloss_ranges.descr, (SELECT (COUNT(*) / 45408) * 100.0 FROM
data WHERE (1.1622*(10.45 + 10*sqrt(windspeed) - windspeed)*(33 -
temp_out)) >= heatloss_ranges.min  AND (1.1622*(10.45 +
10*sqrt(windspeed) - windspeed)*(33 - temp_out)) < heatloss_ranges.max)
AS percentage FROM heatloss_ranges GROUP BY heatloss_ranges.min ORDER BY
percentage DESC;

Output is iets als dit:
+-----------+------------+
| descr     | percentage |
+-----------+------------+
| zeer laag |   71.44556 |
| laag      |   28.34743 |
| gemiddeld |    0.24225 |
| extreem   |    0.00000 |
| zeer hoog |    0.00000 |
| hoog      |    0.00000 |
+-----------+------------+

mysql> describe heatloss_ranges;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| min   | float        | NO   | PRI | NULL    |       |
| max   | float        | NO   | PRI | NULL    |       |
| descr | varchar(128) | NO   |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> describe data;
+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| timestamp      | datetime     | NO   | PRI | NULL    |       |
| temp_in        | decimal(4,1) | NO   |     | 0.0     |       |
| temp_out       | decimal(4,1) | NO   |     | 0.0     |       |
| dewpoint       | decimal(4,1) | NO   |     | 0.0     |       |
| rel_hum_in     | tinyint(3)   | NO   |     | 0       |       |
| rel_hum_out    | tinyint(3)   | NO   |     | 0       |       |
| windspeed      | decimal(4,1) | NO   |     | 0.0     |       |
| wind_direction | char(3)      | NO   |     | NULL    |       |
| wind_angle     | decimal(4,1) | NO   |     | 0.0     |       |
| wind_chill     | decimal(4,1) | NO   |     | 0.0     |       |
| rain_1h        | decimal(6,1) | NO   |     | 0.0     |       |
| rain_24h       | decimal(6,1) | NO   |     | 0.0     |       |
| rain_1w        | decimal(7,1) | NO   |     | 0.0     |       |
| rain_1m        | decimal(7,1) | NO   |     | 0.0     |       |
| rain_total     | decimal(8,1) | NO   |     | 0.0     |       |
| rel_pressure   | decimal(8,1) | NO   |     | 0.0     |       |
| abs_pressure   | decimal(8,1) | NO   |     | 0.0     |       |
| tendency       | varchar(10)  | NO   |     | NULL    |       |
| forecast       | varchar(7)   | NO   |     | NULL    |       |
+----------------+--------------+------+-----+---------+-------+
19 rows in set (0.00 sec)

Die query berekent dus de hoe vaak welk warmteverlies.


Folkert van Heusden

-- 
www.vanheusden.com/multitail - win een vlaai van multivlaai! zorg
ervoor dat multitail opgenomen wordt in Fedora Core, AIX, Solaris of
HP/UX en win een vlaai naar keuze
----------------------------------------------------------------------
Phone: +31-6-41278122, PGP-key: 1F28D8AE, www.vanheusden.com