Re: Big problems on optimizing query

[Dieter Steinwedel]

The MySQL version is 5.1.41 64bit on Ubuntu 10.04. It definitely supports more than 4GB otherwise mysql would not start.

I agree with you
* more fine-tuning for key_buffer_size and data/query cache
* more useful (compound) indexes
* selective denormalization for speeding up
* smaller primary keys

All this stuff can extremely speed up.

The fk-indexes exists because the 1st version was a innodb-version and I did not rename the keys on migrating to myisam.

The essential question is - and not answered till now - why is query q1 (look at 2nd posting of me) so slow and why is not used the available key (even look at 2nd posting of me). Innodb use it in that case. Is it a weakness of myisam on joining tables? Can I and if yes how can enforce the index for q1? Query hints like "use index" or "force index" are ignored.

I want to use myisam because it is (normally) faster than innodb and in my case transactions and constraints are not required.