Solved

MySQL match all words in query

Posted on 2014-07-31
9
193 Views
Last Modified: 2014-08-01
Hi,

I need a query that only returns results from the table where every word in the query is found.

I've got this query so far, but it returns results which only contain some words.

SELECT * FROM variants WHERE MATCH(keywords) AGAINST ('+2.0 +CDTi +SRi' IN BOOLEAN MODE) ORDER BY description2

Open in new window


Can someone tell me where I'm going wrong with the query?
0
Comment
Question by:SheppardDigital
  • 4
  • 4
9 Comments
 
LVL 15

Expert Comment

by:Insoftservice
ID: 40232652
try out this one

SELECT * FROM variants  WHERE MATCH (keywords) AGAINST (' "2.0" ' IN BOOLEAN MODE)

For multiword searches:

SELECT * FROM variants  MATCH (keywords) AGAINST (' "2.0" "CDTi " ' IN BOOLEAN MODE)

SELECT * FROM variants  MATCH (keywords) AGAINST ('+"2.0" +"CDTi " ' IN BOOLEAN MODE)
0
 
LVL 58

Expert Comment

by:Gary
ID: 40232718
Your original query is correct, can you attach a dump of the variants table.
0
 

Author Comment

by:SheppardDigital
ID: 40233602
Hi insoftserver, I tried both of your suggestions and neither worked I'm afraid.

The first suggestion seemed to match single words from those supplied and the 2nd didn't return any results.

Hi Gary, I'm attaching a dump of a selection of rows from the database. What I'm trying to do is only return results which contain all of these words '2.0' 'cdti' 'sri' 'astra'
variants2.sql
0
 
LVL 58

Expert Comment

by:Gary
ID: 40234182
New day and brain is refreshed
In my.ini (or my.conf depending on your server) change

ft_min_word_len=3   <- minimum characters for a match

Restart mysql and rebuild the FULLTEXT index
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:SheppardDigital
ID: 40234214
I've added the line to the end of /etc/my.cnf file. Restarted apache and than ran this query on the database to rebuild the indexes 'REPAIR TABLE <TableName> QUICK;'

The query I'm running on the table now is;

SELECT *,MATCH(keywords) AGAINST ('+2.0 +astra +sri +cdti ' IN BOOLEAN MODE) AS score FROM tmp_variants WHERE MATCH(keywords) AGAINST ('+2.0 +astra +sri +cdti ' IN BOOLEAN MODE) ORDER BY score DESC, description2 ASC

Open in new window


The results better but still don't seem right, here's the first handful of results along with the score.

10.714305877685547 | Vauxhall Astra 1.7 CDTi 16V SRi 3d
10.714305877685547 | Vauxhall Astra 1.7 CDTi 16V SRi 5d
10.714305877685547 | Vauxhall Astra 1.7 CDTi SRi (100ps) 5d
10.714305877685547 | Vauxhall Astra 1.9 CDTi SRi (120ps) 3d
10.714305877685547 | Vauxhall Astra 1.9 CDTi SRi (120ps) 5d
10.714305877685547 | Vauxhall Astra 2.0 CDTi 16V SRi 3d
10.714305877685547 | Vauxhall Astra 2.0 CDTi 16V SRi 5d
10.714305877685547 | Vauxhall Astra 2.0 CDTi 16V SRi 5d
10.600271224975586 | Vauxhall Astra 1.3 CDTi 16V ecoFLEX SRi 5d
10.600271224975586 | Vauxhall Astra 1.3 CDTi 16V ecoFLEX SRi 5d
10.600271224975586 | Vauxhall Astra 1.6 CDTi 16V ecoFLEX SRi 3d
10.600271224975586 | Vauxhall Astra 1.6 CDTi 16V ecoFLEX SRi 5d
10.600271224975586 | Vauxhall Astra 1.6 CDTi 16V ecoFLEX SRi 5d
10.600271224975586 | Vauxhall Astra 1.7 CDTi 16V (130bhp) SRi 3d
10.600271224975586 | Vauxhall Astra 1.7 CDTi 16V ecoFLEX SRi 5d
10.600271224975586 | Vauxhall Astra 1.7 CDTi 16V SRi (100ps) 3d
10.600271224975586 | Vauxhall Astra 1.7 CDTi 16V SRi (100ps) 5d

Open in new window


I'm was expecting the 'Vauxhall Astra 2.0 CDTi 16V SRi 3d' rows to appear at the top as they contain all four words.
0
 

Author Comment

by:SheppardDigital
ID: 40234241
After some research, is it that mysql is ignoring the '2.0' in the search? so it's only matching the remaining three actual words?
0
 
LVL 58

Expert Comment

by:Gary
ID: 40234249
It maybe, I am trying to find something in the docs for it.
0
 
LVL 58

Accepted Solution

by:
Gary earned 500 total points
ID: 40234344
The problem seems to be the period
Mysql is thinking its the end of a line/word and hence doesn't parse 2.0 as one word.
I suppose one way around this is to replace the . with some other character
0
 

Author Comment

by:SheppardDigital
ID: 40234387
Gary,

Yes, you're right.

I've replaced all instances of . with the word 'period' and I'm doing the same when generating the SQL query.

The results returned now are as expected, thank you.

In my case the keyword field is only used for searching and not visible to end users, so I'm able to do this.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

760 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now