slow queries using multi-column index
Posted by:
Jan Zimmek
Date: March 01, 2005 10:03AM
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
Subject
Views
Written By
Posted
slow queries using multi-column index
2896
March 01, 2005 10:03AM
1866
March 03, 2005 07:36PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.