MySQL Forums
Forum List  »  Performance

Demanding Query
Posted by: Tobin Harris
Date: July 23, 2005 11:31AM

Hi there,

I have a demanding query and I'm using EXPLAIN to get some info on the execution plan, but I just can't make this query use an index. First the table...

create table articles
(
id int auto_increment,
url varchar(500),
url_hash varchar(32),
headline varchar(500),
headline_hash varchar(32),
body text
)

The query that is taking too long is shown below (parameters shown starting with a ?)...

SELECT 1
FROM articles
WHERE
(url_hash = md5(?url) AND url = ?url)
OR (headline_hash = md5(?headline) AND headline = ?headline)
OR left(body,900)=left(?body,900)
LIMIT 1;

You can see I'm using hashes to help speed up performance. I'm assuming that the hash isn't *really* unique, so both the hash and the full value itself have to be compared. One index I tried was this...

alter table articles create index idx_articles_1 (url_hash, headline_hash, url(20), headline(10), body(50));

MySql won't use this index, even with over 100,000 records in the table. Query time is about 6 seconds, but I was hoping to get it down to .5 seconds with the use of appropriate indexes.

Any help much appreciated.

Tobin

Options: ReplyQuote


Subject
Views
Written By
Posted
Demanding Query
2513
July 23, 2005 11:31AM
1781
July 23, 2005 01:52PM
1685
July 24, 2005 05:35AM
1649
July 24, 2005 09:39AM
1715
July 24, 2005 11:44AM


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.