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> 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 Picci
BP> Micro srl
BP> viale Marconi 222, 09131 Cagliari - tel. (+39) 070400240
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