> I didn't know that complex "sub-keys" are useful for one-column keys as well.
My understanding is indexes are used from left to right in a complex key. So to get the best performance you want to 're-use' existing indexes as much as possible.
That being said - I have been wrong before ...
So a couple of observations.
First, delete KEY `region` (`region`) - same as above.
Read these:
http://dev.mysql.com/doc/refman/5.0/en/group-by-optimization.html
http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html
I notice `kody_regionow`.`kod` is unique. Is that the parent row in the 'relationship'. For each kod there could be many flights?
Admittedly, understanding the data and thinking in terms of sets of data are a weakness for me.
However I think you want to restrict the rows as much as possible before you do your order by or group by.
I am not sure how many results you get with your query since your flights table has a fair number of rows (~3 million) but if you can filter anything out between the join condition and the order/group by's you'll be helping a lot. (I think)
Here are two queries that *may* be better than what you were running.
EXPLAIN SELECT f.kod_regionu, k.panstwo AS nazwa_panstwa, k.region AS nazwa_regionu, f.kod_panstwa_ISO AS kod_panstwa, COUNT( DISTINCT f.kod_obiektu) AS i_ofert
FROM flights f
JOIN kody_regionow k
ON f.kod_regionu = k.kod
GROUP BY f.kod_panstwa_ISO, f.kod_regionu
ORDER BY k.panstwo, k.region ASC;
-- also try
EXPLAIN SELECT f.kod_regionu, k.panstwo AS nazwa_panstwa, k.region AS nazwa_regionu, f.kod_panstwa_ISO AS kod_panstwa, COUNT( DISTINCT f.kod_obiektu) AS i_ofert
FROM kody_regionow k
JOIN flights f
ON k.kod = f.kod_regionu
GROUP BY f.kod_panstwa_ISO, f.kod_regionu
ORDER BY k.panstwo, k.region ASC;
Post back the explain results for these.
Shawn