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.