[PHP] Mysql search query ignoring dots
Hi all,
I have to perform a mysql query in a table with millions of records.
I've full-text indexed my search field and I'm searching with MATCH AGAINST.
But there is a problem. In this field there are company names that
contain dots, for istance I've PO.SE. srl and I want to find it if
the user search for: POSE or PO.SE or P.O.S.E. etc.
I googled in the web but I don't find any solution. I don't want to
add a new field with the cleaned version of my string because I would
like to solve with the query and I prefer that the mysql table not
become too big. But if I will not find a different solution, I will
use this escamotage.
I've find a post that is similar but the solution don't seem to solve
my situation.
You can see it at the url:
http://forums.mysql.com/read.php?10,395557,395584#msg-395584
In my case replace(email, '.', '') = replace(theSearchValue, '.', '');
is indifferent and don't change my results.
My query, searching POSE, is:
select aziende.* from aziende where 10 AND
(MATCH(aziende.ragione_sociale) AGAINST('+POSE' IN BOOLEAN MODE) OR
(replace(aziende.ragione_sociale, '.', '') = replace('POSE', '.',
'')) order by aziende.ragione_sociale limit 0, 10
The alternative choice could be REGEXP but I've red that it make my
query slow in a table of millions of records and I don't know how to
exclude dots in the regular expression.
Can anyone help me?
Thanks in advance.
Barbara
--
Barbara Picci
Micro srl
viale Marconi 222, 09131 Cagliari - tel. (+39) 070400240
http://www.microsrl.com
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Mysql search query ignoring dots
-Original Message-
From: Barbara Picci [mailto:barbara.pi...@sardi.it]
Sent: Monday, January 24, 2011 4:51 AM
To: php-general@lists.php.net
Subject: [PHP] Mysql search query ignoring dots
Hi all,
I have to perform a mysql query in a table with millions of records.
I've full-text indexed my search field and I'm searching with MATCH
AGAINST.
But there is a problem. In this field there are company names that contain
dots, for istance I've PO.SE. srl and I want to find it if the user
search for:
POSE or PO.SE or P.O.S.E. etc.
I googled in the web but I don't find any solution. I don't want to add a
new
field with the cleaned version of my string because I would like to solve
with the query and I prefer that the mysql table not become too big. But
if I
will not find a different solution, I will use this escamotage.
I've find a post that is similar but the solution don't seem to solve my
situation.
You can see it at the url:
http://forums.mysql.com/read.php?10,395557,395584#msg-395584
In my case replace(email, '.', '') = replace(theSearchValue, '.', ''); is
indifferent and don't change my results.
My query, searching POSE, is:
select aziende.* from aziende where 10 AND
(MATCH(aziende.ragione_sociale) AGAINST('+POSE' IN BOOLEAN MODE) OR
(replace(aziende.ragione_sociale, '.', '') = replace('POSE', '.',
'')) order by aziende.ragione_sociale limit 0, 10
The alternative choice could be REGEXP but I've red that it make my query
slow in a table of millions of records and I don't know how to exclude
dots
in the regular expression.
Can anyone help me?
Thanks in advance.
Barbara
--
Barbara Picci
Micro srl
viale Marconi 222, 09131 Cagliari - tel. (+39) 070400240
http://www.microsrl.com
I don't see anything relevant regarding PHP. As for ' a table of millions
of records,' that sounds like questions for the DBA.
Regards,
Tommy
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql search query ignoring dots
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 10 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
