Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MySql Boolean Search on #/+ special characters

Posted on 2011-09-23
7
Medium Priority
?
509 Views
Last Modified: 2012-06-27
We need to be able to search for the + sign and # sign in MySQL using MATCH AGAINST IN BOOLEAN MODE, but these characters are not apparently searchable by default, which means we cant search for c++ or c# in boolean mode.  I have gone and read the MySQL documentation on this matter but to be honest, its not in laymen's terms and I am shocked by the lack of information on this subject, I would have thought this would have been a common requirement.

I have also just found out, that if we want it to search for + and # and use UTF8 that we have to modify the source code for mysql!!  Surely this cant be true?  Im sure someone out there knows of a solution to this, there must be, other MySQL is apparently not the right choice here.
0
Comment
Question by:recruitit
  • 4
  • 3
7 Comments
 
LVL 24

Assisted Solution

by:johanntagle
johanntagle earned 2000 total points
ID: 36585489
Sounds like you are building a search engine for a jobs site ? =).  Anyway, no, MySQL full text search is still very much lacking in terms in functionality and performance.  Suggest you look into Sphinx Search (http://sphinxsearch.com/) and other 3rd party search engines.
0
 

Author Comment

by:recruitit
ID: 36585548
ok, thanks for the feedback, im going to have to write a quick temporary hack for converting it into a bunch of LIKES with wildcards, which im sure isnt going to be greatly performant considering the possible complexity lol, but changing the database isnt really a feesable option at the moment, will have to plan for the future there!  Thanks again
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 36585549
If you still want to try MySQL full text, somebody said this in http://stackoverflow.com/questions/584976/how-do-you-get-your-fulltext-boolean-search-to-pick-up-the-term-c, don't know if it actually works:

From http://dev.mysql.com/doc/refman/5.0/en/fulltext-boolean.html:

"A phrase that is enclosed within double quote (“"”) characters matches only rows that contain the phrase literally, as it was typed."

This means you can search for 'C++' using this query:

SELECT * FROM mytable WHERE MATCH (field1, field2, field3) AGAINST ('"C++"' IN BOOLEAN MODE)
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:recruitit
ID: 36585571
oh nice, ill have a look
0
 
LVL 24

Accepted Solution

by:
johanntagle earned 2000 total points
ID: 36585612
Definitely using LIKE is a performance killer.  I'd rather do some other hack like convert "+" to "plus" when stored to the database and also when made part of a search criteria.  I think your best bet is still to use something like Sphinx - that will take some work, but far less work than changing databases.
0
 

Author Comment

by:recruitit
ID: 36585712
sorry searching by phrase doesnt work :( thanks though
0
 

Author Comment

by:recruitit
ID: 36585748
ok, we are going to use the conversion method, that will be ok for awhile, thanks!
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What we learned in Webroot's webinar on multi-vector protection.
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question