MySQL Forums
Forum List  »  Performance

Wildcard searches on big table
Posted by: Kjartan Kvinsvik
Date: January 29, 2005 06:05PM

I need some help on improving performance on wildcard searches (f.ex. WHERE somefield LIKE '%foobar%'). The table contains around 25m records, and has the following structure (MyIsam):
domain | varchar(64)
tld | tinyint(3)
date | date

Currently a wildcard search on the domain field takes around 60 seconds, which is way to slow. An example search would be SELECT domain FROM table WHERE domain like %abc%. This is on an Athlon64 3200+ with 1gb of RAM, running XP. I am relatively new to database design, so I could use some help on this one. I tried using a memory table, but it still took around 5 seconds. And that was with only 5m records (couldn't have more with only 1gb of RAM).

I know that with the right setup and design, it should be possible to perform a query like this in around 5 seconds on 30m records. A competitor of mine is doing this here:

Any help/advice greatly appreciated.

Options: ReplyQuote

Written By
Wildcard searches on big table
January 29, 2005 06:05PM
January 30, 2005 12:05AM

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.