?
Solved

MySQL match all words in query

Posted on 2014-07-31
9
Medium Priority
?
206 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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 2000 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
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

762 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