I’m going out on a limb to say MySQL’s full-text indexing and searching features are underused. They appeared in MySQL 3.23.23 (most people are using 4.x, and 5 is in development), but it’s been news to most of the people I know.
Here’s the deal, the MATCH() function can search a full-text index for a string of text (one or more words) and return relevance-ranked results. It’s at the core of the list of related links at the bottom of every post here.
For that query, I put all the tag names into a single variable that might look like this:
$keywords = “mysql database php select full-text search full-text searching docs documentation”
Then I do a select that looks something like this:
SELECT * FROM wp_posts WHERE MATCH(post_title,post_content) AGAINST(’$keywords’);
The docs give a lot more detail, including how to do boolean searches.
tags: boolean search, boolean searches, boolean searching, database, db, docs, documentation, full text, full text index, full text search, full text searching, fulltext, fulltext search, keywords, match(), mysql, rank, relevance, relevance rank, relevance ranked, relevance ranking, search, search full text
Posted September 6, 2005 by Casey Bisson
Categories: Technology. Tags: boolean search, boolean searches, boolean searching, database, db, docs, documentation, full text, full text index, full text search, full text searching, fulltext, fulltext search, keywords, match(), mysql, rank, relevance, relevance rank, relevance ranked, relevance ranking, search, search full text.
8 Comments
Comments RSS
TrackBack Identifier URI
User contributed tags for this post:
gogle com (6760) - gogle (6726) - googleheart (559) - gogle earth com (409) - mysql search relevance (367) - MySQL relevance (326) - mysql relevance search (286) - mysql fulltext relevance (237) - gogle it (236) - gogle earth (221) - WWW GOGLE COM (139) - WWW GOGLE (123) - mysql MATCH relevance (105) - WWW GOGLE EARTH COM (101) - Google Earth com (99) - earth gogle com (99) - goegle (90) - mysql full text relevance (82) - mysql match against relevance (81) - relevance mysql (78) - goegle com (78) - mysql fulltext search relevance (75) - fulltext relevance (74) - mysql like relevance (73) - mysql full text search relevance (70) - mysql search by relevance (59) - mysql full text search (56) - mysql fulltext (52) - mysql fulltext score (47) - gogle de (46) - php mysql search relevance (46) - mysql 5 full text search (41) - mysql fulltext weight (41) - goegle earth (41) - gogle com in (38) - mysql Relevance Ranking (38) - mysql full text score (37) - mysql search relevancy (37) - mysql (35) - mysql fulltext ranking (35) - MySQL Full Text Search score (34) - relevance search mysql (32) - mysql fulltext boolean relevance (32) - google S com (32) - mysql natural language search (31) - goegle earth com (30) - mysql as relevance (29) - fulltext mysql (29) - mysql relevance score (29) - mysql Boolean Full Text Searches (29) - mysql relevancy search (29) - 2786506456964 (28) - mysql relevancy (27) - mysql fulltext relevancy (26) - WWW GOGLE EARTH (25) - www.goegle.com (25) - full text search relevance (24) - shemalles (23) - gogle com eart (23) - fulltext search relevance (22) - mysql 5 full text (22) - mysql full text relevancy (22) - MySQL Natural Language (22) - w w w gogle com (21) - mysql ranked search (21) - fulltext mysql relevance (21) - mysql full text weight (21) - WWW GOOGLE EARTH COM (20) - earth gogle (20) - http earth gogle com (20) - mysql 4 full text search (20) - mysql like (20) - full text mysql (20) - gogle full (20) - azdevag (19) - relevance in mysql (19) - relevance search in mysql (19) - relevance (18) - search relevance mysql (18) - www goegle com (18) - mysql search with relevance (17) - mysql boolean relevance (16) - mysql 5 fulltext (16) - 3038451802374 (16) -
[...] Why? Because MySQL 3.x doesn’t support query caching, boolean full-text searching, or complex subqueries. [...]
[...] MySQL provides two types of fulltext searches – boolean and natural language. I’m going to focus on the natural language search because it is more mathematically intense. The underlying concept behind the method used in MySQL is that each term in each document is assigned a specific weight which is used to decide a query’s “distance” or “score” with respect to that document. The weights are assigned such that the weight is increased if the term occurs frequently in the document, but decreased in the term occurs frequently among all documents. For a description of how the weights are computed, check out the MySQL documentation. For the curious reader, this article also explains the computation of word-document weights. There are also a slew of articles on using fulltext search in practice. [...]
[...] MySQL provides two types of fulltext searches – boolean and natural language. I’m going to focus on the natural language search because it is more mathematically intense. The underlying concept behind the method used in MySQL is that each term in each document is assigned a specific weight which is used to decide a query’s “distance” or “score” with respect to that document. The weights are assigned such that the weight is increased if the term occurs frequently in the document, but decreased in the term occurs frequently among all documents. For a description of how the weights are computed, check out the MySQL documentation. For the curious reader, this article also explains the computation of word-document weights. There are also a slew of articles on using fulltext search in practice. [...]
[...] And I’m fully confident that when I put our entire catalog into WPopac, all 330,000 bib records (resulting in about 6.2 million atomic records), performance will still be up to the task. And my math suggests everything should be ducky on a relatively budget server up beyond about 1 million bib records), but what happens for libraries that have more than that, say, perhaps 6 to 8 million bib records (again, 110 to 150 million atomic records; again, all full-text indexed in MySQL)? [...]
salam dostaneh man harkasi keh mikad ba yek pesarehg 29 saleh mogarad va lisanseh mekanik az thran azdevag koneh ageh be tafahoom residim baram emall bezareh
[...] MySQL provides two types of fulltext searches – boolean and natural language. I’m going to focus on the natural language search because it is more mathematically intense. The underlying concept behind the method used in MySQL is that each term in each document is assigned a specific weight which is used to decide a query’s “distance” or “score” with respect to that document. The weights are assigned such that the weight is increased if the term occurs frequently in the document, but decreased in the term occurs frequently among all documents. For a description of how the weights are computed, check out the MySQL documentation. For the curious reader, this article also explains the computation of word-document weights. There are also a slew of articles on using fulltext search in practice. [...]
sexy gierls
Thanks Maison, was looking for it. I wanted to sort the results of SELECT query by releveance. But I was stuck with ‘%LIKE%’. You have helped me out.
Thanks a lot dear!