MySQL Forums
Forum List  »  Full-Text Search

FullText Searches with Email Addresses
Posted by: Paul Thompson
Date: June 05, 2008 01:59PM

I have a problem with a table that I'm working on. There are around 5M rows of data in a table with this structure:

+---------+-------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------------------+-------+
| day | datetime | NO | | 0000-00-00 00:00:00 | |
| person | text | NO | MUL | NULL | |
| email | text | NO | MUL | NULL | |
| pid | int(11) | NO | | 0 | |
+---------+-------------+------+-----+---------------------+-------+

person and email are FULLTEXT indices.

I'm doing this search on the data:

select * FROM `table` WHERE MATCH (`email`) AGAINST ('+firstname.lastname@hotmail.com');

This should return around 1900 rows. It takes a very long time to search. (45 seconds)

If I remove the +, and use select * FROM `table` WHERE MATCH (`email`) AGAINST ('firstname.lastname@hotmail.com') it's very fast.

Now, if a user wants to search on the domain portion of the email address:

select * FROM `table` WHERE MATCH (`email`) AGAINST ('+hotmail.com');

This search is very slow (around 7 minutes).

If I modify those to use IN BOOLEAN MODE, then it gets even slower, and the domain part doesn't work at all.

Is there something I'm missing here?

Options: ReplyQuote


Subject
Views
Written By
Posted
FullText Searches with Email Addresses
9571
June 05, 2008 01:59PM


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.