Solved

MySQL match all words in query

Posted on 2014-07-31
9
204 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
[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
  • 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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
 

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

717 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