Hi, 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 too. -- regards, Tom -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
