Solved

MySQL match all words in query

Posted on 2014-07-31
9
198 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

816 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

11 Experts available now in Live!

Get 1:1 Help Now