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

x
?
Solved

Using wildcards in MySQL fulltext searches

Posted on 2008-10-02
6
Medium Priority
?
1,719 Views
Last Modified: 2012-05-05
I'm using a MySQL database, and I have a table containing a text field. I was using a query to return all rows containing a string within the text field -

... WHERE desc like '%MSDN%'

But this is was too slow on a large table. I speeded this up by using a fulltext index on the 'desc' column in my table.
However, the equivalent fulltext search clause

... MATCH desc AGAINST ('MSDN')

returns rows matching

...w/MSDN Prem...

but not

...w/MSDNPrem...

I've tried modifying my fulltext search with wildcards i.e.

... MATCH desc AGAINST ('*MSDN*')

but this didn't work, I still didn't get the complete set of matches I expected. Does anyone know what the equivalent fulltext search pattern is, to return exactly the same resultset as my original query?
0
Comment
Question by:smartstream-stp
[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
  • 3
  • 2
6 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22622620
what about:
... MATCH desc AGAINST ('MSDN*')

Open in new window

0
 

Author Comment

by:smartstream-stp
ID: 22622740
Unfortunately that doesn't work. 'MSDN*' does not return rows containing '...w/MSDNPrem...'
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22622769
first you cannot use *something in FT...

so, I am not sure, but maybe / is not a word breaker....
http://dev.mysql.com/doc/refman/5.0/en/fulltext-fine-tuning.html

#If you want to change the set of characters that are considered word characters, you can do so in two ways. Suppose that you want to treat the hyphen character ('-') as a word character. Use either of these methods:
 
    *    Modify the MySQL source: In myisam/ftdefs.h, see the true_word_char() and misc_word_char() macros. Add '-' to one of those macros and recompile MySQL.
 
    *    Modify a character set file: This requires no recompilation. The true_word_char() macro uses a character type table to distinguish letters and numbers from other characters. . You can edit the <ctype><map> contents in one of the character set XML files to specify that '-' is a letter. Then use the given character set for your FULLTEXT indexes.
 
After making the modification, you must rebuild the indexes for each table that contains any FULLTEXT indexes.

Open in new window

0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 5

Accepted Solution

by:
jericotolentino earned 1500 total points
ID: 22622902
Try if MySQL boolean search works for you:

SELECT *,MATCH(desc) AGAINST ('*MSDN*' IN BOOLEAN MODE) AS score FROM search WHERE MATCH(desc) AGAINST ('*MSDN*' IN BOOLEAN MODE);

It requires that you're using MyISAM and the indexes you're searching are of type varchar or text.

More info is available here:
http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html
0
 

Author Comment

by:smartstream-stp
ID: 22622945
If '/' isn't a word breaker, then how is 'w/MSDN Prem' matched, but not 'w/MSDNPrem' ?

I don't think fulltext indexing is going to be 100% reliable for me, as it relies on the text field being split into words. In my case I can't guarantee this. I don't think there is any direct equivalent for 'WHERE desc LIKE '%MSDN%' i.e. matching an aribitrary string within a text field.

0
 

Author Comment

by:smartstream-stp
ID: 22622997
jericotolentino's suggestion worked much better, but didn't match a field value of

VStudio Team Ed Sft Test AllLngLic/SAOLVNL1YRAcqY1wMSDNPrmAdPrdPrtnrOnly
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
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…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

721 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