Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MySql Boolean Search on #/+ special characters

Posted on 2011-09-23
7
Medium Priority
?
504 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

722 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