[linux] mysql & filesort

[Folkert van Heusden]

Hi,

Er gebeurd iets geks met mijn MySQL installatie.
Als volgt:
Ik heb een simpele query:
SELECT temp_out FROM data ORDER BY temp_out;
als ik vervolgens aan mysql vraag hoe e.e.a. die dat uitvoert krijg ik
dit:
mysql> explain SELECT temp_out FROM data ORDER BY temp_out;                                                                                                                  +----+-------------+-------+------+---------------+------+---------+------+-------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+-------+----------------+
|  1 | SIMPLE      | data  | ALL  | NULL          | NULL | NULL    | NULL | 53943 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+-------+----------------+
1 row in set (0.00 sec)

Dat is gek: met een filesort!
Dat is niet goed want tra(a)g(er).

Ik doe nu dus niet moeilijke group-bys, joins of wat dan ook.
Iemand enig idee wat dit kan veroozaken?

Nog wat extra gegevens:

- systeem heeft 2GB ram waarvan 1.1GB vrij(!) (nu dus als cache in
  gebruik natuurlijk)
- 54000 rows
- tabel gebruikt 6.5MB diskspace
- vrij simpele tabel zonder moeilijke relaties e.d.:
 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.01 sec)

- sort buffer van 8MB:
 mysql> show variables like '%sort%';
 +---------------------------+------------+
 | Variable_name             | Value      |
 +---------------------------+------------+
 | max_length_for_sort_data  | 1024       |
 | max_sort_length           | 1024       |
 | myisam_max_sort_file_size | 2147483647 |
 | myisam_sort_buffer_size   | 8388608    |
 | sort_buffer_size          | 2097144    |
 +---------------------------+------------+
 5 rows in set (0.00 sec)

- database is MyISAM:
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


Folkert van Heusden

-- 
--------------------------------------------------------------------
Phone: +31-6-41278122, PGP-key: 1F28D8AE, www.vanheusden.com