I have a database of 65,000,000 domain names in MySQL.  Is there an
easy way to seach for a name within this list? I need a decent query.
IE: 
query jesus would find
jesus.com
jesussaves.com
iamjesuschrist.com


Using LIKE is way too slow and using MATCH AGAINST doesn't work (it's
specific to words not single characters). I need something that gives
the output like my first example in under 2 seconds.

Thanks

---

Clarification of Question by dcaban-ga on 31 Mar 2006 10:56 PST

In the database is:
jesus
jesussaves
iamjesuschrist
i-am-jesus

Right now if I query jesus with MATCH AGAINST..
The result I get is:
jesus
i-am-jesus


I basically need my query to search for words within a blob of
characters. I assume MATCH AGAINST is only looking for words. Not
words within a blob of characters.

Hello dcaban,

Can you explain this portion to me , with a little more detail :

"using MATCH AGAINST doesn't work (it's specific to words not single characters)"

Do you mean that you want a search for - let's say 'B', to return
results for 'B'? If that is correct, then your results may be the
expected outcome. This is due to the MySQL default fulltext setting
for 'ft_min_word_length'.

You would need to change that to '1', and then repair your index. You
would probably also want to ensure that you are not using the stopword
list for fulltext, but we would need more information on your desired
output for any given search to be certain.

Maybe a few more details would help us to better assist you. Thanks!

SgtCory

In the database is:
jesus
jesussaves
iamjesuschrist
i-am-jesus

Right now if I query jesus with MATCH AGAINST..
The result I get is:
jesus
i-am-jesus


I basically need my query to search for words within a blob of
characters. I assume MATCH AGAINST is only looking for words. Not
words within a blob of characters.

Hello,

It sounds like you want to use the boolean search feature. You can
read more about this here :

Fulltext Boolean Searches
http://dev.mysql.com/doc/refman/4.1/en/fulltext-boolean.html

In your case, you would want to search for '*jesus*', and your query
would look something like this :

SELECT * FROM yourdata WHERE MATCH (index1,index2 etc...) AGAINST
('*jesus*' IN BOOLEAN MODE);

I get the same results in BOOLEAN mode as I do with a regular MATCH AGAINST.