slow queries using multi-column index

[Jan Zimmek]

Currently we're migrating some of out read-only(search) tables from Oracle 9 to MySQL 4. Porting table-structure and data was not a problem, but some of our queries (multi-column indexed) are really slow when executed.

Some data of environment:
=========================
MySQL-Engine: MyISAM
Table-Count: 1
Column-Count: 15
Row-Count: ~ 3.000.000

running on P4 2.8 Ghz / 1GB RAM / Windows XP

The following columns are relevant:
-----------------------------------
id - DOUBLE
photo - DOUBLE
appdomain_id - DOUBLE
country - VARCHAR(2)
registered - DATETIME


This SQL is running really slow (1st -> 70 sec. / any further -> ~ 10 sec):
--------------------------------------------------------------------------
select id from d_quicksearch2 use index (IDX_COMPLEX_REGISTERED) where photo > -2 and APPDOMAIN_ID = 0 and COUNTRY = 'DE' and REGISTERED > date_sub(curdate(), interval 7 day) limit 20;


Index 'idx_complex_registered':
-------------------------------
create index IDX_COMPLEX_REGISTERED on d_quicksearch2 (photo desc, appdomain_id, country, registered desc);


The main-problem is that we have to do sorting within the index.


Can the query be optimized in any way ?

Which configuration-parameter should be changed ?


Thanks for any help in advance.


Jan
Zimmek