[BNM] MySQL FULLTEXT search with email addresses

[Alex Bovey]

Hi all,

I've got a MyISAM table with about 5 million rows and I need to improve
search performance on a TEXT field which contains a variety of data.

I figured a FULLTEXT index was the way to go but I'm finding that it's
treating the ats and dots as spaces - e.g. this returns rows with
b...@smith.com but also just Bob.

SELECT value FROM MyTable WHERE MATCH(value) AGAINST ("b...@smith.com" IN
BOOLEAN MODE);

I have googled it but weirdly I can't find anyone with a solution to what
must be a common problem.

Is there a workaround, or is there a better way to index the table?
Reorganising the data isn't a realistic option sadly.

Thanks all,

Alex

--
Alex Bovey
Web Developer | Alex Bovey Consultancy Ltd
Registered in England & Wales no. 6471391 | VAT no. 934 8959 65
--

BNM Subscribe/Unsubscribe:
http://www.brightonnewmedia.org/options/bnmlist

BNM powered by Wessex Networks:
http://www.wessexnetworks.com

[Toby Skinner]

Have you tried encapsulating the phrase in quotes?

SELECT value FROM MyTable WHERE MATCH(value) AGAINST ('"b...@smith.com"' IN BOOLEAN MODE);

Toby

On 23/05/2011 16:08, Alex Bovey wrote:
> Hi all,
>
> I've got a MyISAM table with about 5 million rows and I need to improve
> search performance on a TEXT field which contains a variety of data.
>
> I figured a FULLTEXT index was the way to go but I'm finding that it's
> treating the ats and dots as spaces - e.g. this returns rows with
> b...@smith.com but also just Bob.
>
> SELECT value FROM MyTable WHERE MATCH(value) AGAINST ("b...@smith.com" IN
> BOOLEAN MODE);
>
> I have googled it but weirdly I can't find anyone with a solution to what
> must be a common problem.
>
> Is there a workaround, or is there a better way to index the table?
> Reorganising the data isn't a realistic option sadly.
>
> Thanks all,
>
> Alex
>

--
Toby Skinner
Global Optima
+44 (0)7971 076226

Portfolio
http://www.globaloptima.co.uk

Linked In
http://uk.linkedin.com/pub/toby-skinner/1b/313/620

Ajeva
http://ajeva.com/pros/NDY5OA==

[Alex Bovey]

On Mon, May 23, 2011 at 4:23 PM, Toby Skinner <to...@globaloptima.co.uk> wrote:
> Have you tried encapsulating the phrase in quotes?
>
> SELECT value FROM MyTable WHERE MATCH(value) AGAINST ('"b...@smith.com"' IN BOOLEAN MODE);

Thanks Toby - that appears to be answer!

--
Alex Bovey
Web Developer | Alex Bovey Consultancy Ltd
Registered in England & Wales no. 6471391 | VAT no. 934 8959 65