CREATE TABLE `flights` (
`kod_panstwa_ISO` char(2) COLLATE utf8_polish_ci NOT NULL,
`kod_regionu` varchar(3) COLLATE utf8_polish_ci DEFAULT NULL,
`data_start` varchar(10) COLLATE utf8_polish_ci DEFAULT NULL,
`period` int(3) DEFAULT NULL,
`catalog` varchar(4) COLLATE utf8_polish_ci DEFAULT NULL,
`kod_obiektu` varchar(5) COLLATE utf8_polish_ci DEFAULT NULL,
`cena` int(10) DEFAULT NULL,
`lotnisko_wylotu` varchar(3) COLLATE utf8_polish_ci DEFAULT NULL,
KEY `kod_panstwa_ISO` (`kod_panstwa_ISO`),
KEY `kod_regionu` (`kod_regionu`),
KEY `data_start` (`data_start`),
KEY `period` (`period`),
KEY `catalog` (`catalog`),
KEY `kod_obiektu` (`kod_obiektu`),
KEY `lotnisko_wylotu` (`lotnisko_wylotu`),
KEY `cena` (`cena`),
KEY `mainIdx` (`kod_panstwa_ISO`,`period`,`catalog`,`data_start`),
KEY `mainIdx2` (`kod_panstwa_ISO`,`period`,`catalog`,`lotnisko_wylotu`),
KEY `shortIdx` (`catalog`,`kod_obiektu`),
KEY `dataCatalogIdx` (`data_start`,`catalog`),
KEY `catalogRegionIdx` (`kod_regionu`,`catalog`),
KEY `dlaPeriodow` (`kod_panstwa_ISO`,`catalog`,`kod_obiektu`),
KEY `lista_obiektow` (`kod_panstwa_ISO`,`data_start`,`catalog`,`kod_obiektu`),
KEY `lista_obiektow2` (`kod_regionu`,`catalog`,`kod_obiektu`),
KEY `lista_przeloty` (`data_start`,`catalog`,`lotnisko_wylotu`,`kod_obiektu`),
KEY `dlaPeriodow2` (`period`,`catalog`,`kod_obiektu`),
KEY `kod_panstwa_ISO_2` (`kod_panstwa_ISO`,`kod_regionu`)
) ENGINE=MyISAM
| flights | MyISAM | 10 | Dynamic | 3151841 | 43 | 138188164 | 281474976710655 | 238188544 | 0 | NULL | 2010-12-01 08:23:22 | 2010-12-01 08:44:29 | 2010-12-01 08:44:28 | utf8_polish_ci | NULL | | |
explain SELECT kod_regionu, panstwo as nazwa_panstwa, region as nazwa_regionu, NN_dostepnosci_obiektow.kod_panstwa_ISO as kod_panstwa, COUNT( DISTINCT kod_obiektu) as i_ofert FROM NN_dostepnosci_obiektow, kody_regionow WHERE kod = kod_regionu GROUP BY NN_dostepnosci_obiektow.kod_panstwa_ISO, kod_regionu ORDER BY nazwa_panstwa, nazwa_regionu ASC;
+----+-------------+-------------------------+--------+----------------------------------------------+------+---------+------------------------------------------------+---------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------------------+--------+----------------------------------------------+------+---------+------------------------------------------------+---------+---------------------------------+
| 1 | SIMPLE | flights | ALL | kod_regionu,catalogRegionIdx,lista_obiektow2 | NULL | NULL | NULL | 3151841 | Using temporary; Using filesort |
| 1 | SIMPLE | kody_regionow | eq_ref | kod | kod | 9 | neckermann.flights.kod_regionu | 1 | Using where |
+----+-------------+-------------------------+--------+----------------------------------------------+------+---------+------------------------------------------------+---------+---------------------------------+
2 rows in set (0.00 sec)
mysql> show variables like '%buffer%';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_size | 8388608 |
| innodb_log_buffer_size | 1048576 |
| join_buffer_size | 131072 |
| key_buffer_size | 262144000 |
| myisam_sort_buffer_size | 134217728 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 65536 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 500000 |
| sql_buffer_result | OFF |
+-------------------------+-----------+
12 rows in set (0.00 sec)
Thanks in advance for help.