Monday, January 24, 2011, 10:50:41 PM, you wrote:
BP> Hi all,

BP> I have to perform a mysql query in a table with millions of records.
BP> I've full-text indexed my search field and I'm searching with MATCH AGAINST.
BP> But there is a problem. In this field there are company names that 
BP> contain dots, for istance I've "PO.SE. srl" and I want to find it if 
BP> the user search for: "POSE" or "PO.SE" or "P.O.S.E." etc.
BP> I googled in the web but I don't find any solution. I don't want to 
BP> add a new field with the cleaned version of my string because I would 
BP> like to solve with the query and I prefer that the mysql table not 
BP> become too big. But if I will not find a different solution, I will 
BP> use this escamotage.
BP> I've find a post that is similar but the solution don't seem to solve 
BP> my situation.
BP> You can see it at the url:
BP> http://forums.mysql.com/read.php?10,395557,395584#msg-395584
BP> In my case replace(email, '.', '') = replace(theSearchValue, '.', '');
BP> is indifferent and don't change my results.

BP> My query, searching "POSE", is:

BP> select aziende.* from aziende where 1>0 AND 
BP> (MATCH(aziende.ragione_sociale) AGAINST('+POSE' IN BOOLEAN MODE) OR 
BP> (replace(aziende.ragione_sociale, '.', '') = replace('POSE', '.', 
BP> '')) order by aziende.ragione_sociale limit 0, 10

BP> The alternative choice could be REGEXP but I've red that it make my 
BP> query slow in a table of millions of records and I don't know how to 
BP> exclude dots in the regular expression.

BP> Can anyone help me?

BP> Thanks in advance.
BP> Barbara

BP> -- 
BP> ------------------------
BP> Barbara Picci
BP> Micro srl
BP> viale Marconi 222, 09131 Cagliari  - tel. (+39) 070400240
BP> http://www.microsrl.com

In  the interest of speed it may be worth creating a table with just a
link id and the text to search which you can cleanup before inserting.
It will probably save you a headache in the future and will be quicker
than complicated queries.

The list of possible ids can then be tested against the full table for
any other criteria which if the table is indexed properly will be fast


PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to