Skip navigation links

MySQL Forums :: Performance :: Wildcard searches on big table


Advanced Search

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: http://www.deleteddomains.com/search.php

Any help/advice greatly appreciated.

Options: ReplyQuote


Subject Views Written By Posted
Wildcard searches on big table 2935 Kjartan Kvinsvik 01/29/2005 06:05PM
Re: Wildcard searches on big table 1592 Peter Zaitsev 01/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.