MySQL Forums
Forum List  »  Performance

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

Options: ReplyQuote


Subject
Views
Written By
Posted
slow queries using multi-column index
2741
March 01, 2005 10:03AM


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.