Services for searching keywords. Improving the relevance of search in sphinxsearch Wood pigeon search php keywords

I have title (varchar), description (text), keywords (varchar) fields in my mysql table.

I kept keywords field as I thought I would be searching in this field only. But I now require to search among all three fields. so for keywords "word1 word2 word3", my query becomes

SELECT * FROM myTable WHERE (name LIKE "%word1%" OR description LIKE "%word1%" OR keywords LIKE "%word1%" OR name LIKE "%word2%" OR description LIKE "%word2%" OR keywords LIKE "% word2%" OR name LIKE "%word3%" OR description LIKE "%word3%" OR keywords LIKE "%word3%") AND status = "live"

Looks a bit messy but this works. But now I need to implement synonym search. so for a given word assuming there are a few synonyms available this query becomes more messy as I loop through all of the words. As the requirements are getting clearer, I will need to join this myTable to some other tables as well.

Do you think the above way is messy and will cause problems as the data grow?

How can I avoid above mess? Is there any cleaner solution I can go by? Any example will help me.

• Is there any other method/technique you can recommend to me?

EDIT

@Peter Stuifzand suggested me that I could create one search_index table and store all 3 fields (title,keyword,desc) info on that and do full text search. I understand that additionally this table will include reference to myTable primary key as well.

But my advanced search may include joining mytable with Category table, geographic_location table (for searching within 10, 20 miles etc), filtering by someother criteria and of course, sorting of search results. Do you think using mysql fulltext will not slow it down?

By Ibrahim Diallo

Published Jul 2 2014 ~ 16 minutes read

Search is an important feature on a website. When my few readers want to look for a particular passage on my blog, they use the search box. It used to be powered by Google Search, but I have since then changed it to my own home-brewed version not because I can do better but because it was an interesting challenge.

If you are in a hurry and just want your site to be searchable, well do what I did before, use Google.

// In search.php file $term = isset($_GET["query"])?$_GET["query"]: ""; $term = urlencode($term); $website = urlencode("www.yourwebsite.com"); $redirect = "https://www.google.com/search?q=site%3A($website)+($term)"; header("Location: $redirect"); exit;

What it does is pretty simple. Get the term passed by the user, and forward it to Google search page. Limit the search result to our current domain using the site: keyword in the search query. All your pages that are indexed by Google will be available through search now. If you do want to handle your search in house however, then keep reading.

Homemade Search Solution

Before we go any further, try using the search box on this blog. It uses the same process that I will describe below. If you feel that this is what you want then please continue reading.

This solution is catered to small websites. I make use of LIKE with wild cards on both ends, meaning your search cannot be indexed. This means the solution will work fine for your blog or personal website that doesn't contain tons of data. Port it to a bigger website and it might become very slow. MySQL offers Full Text Search which is not what we are doing here.

Note: If you have 5000 blog posts you are still fine. .

We will take the structure of this blog as a reference. Each blog post has:

• A title p_title
• A url p_url
• A summary p_summary
• A post content p_content
• And catergories category.tagname

For every field that matches with our search term, we will give it a score. The score will be based on the importance of the match:

// the exact term matches is found in the title $scoreFullTitle = 6; // match the title in part $scoreTitleKeyword = 5; // the exact term matches is found in the summary $scoreFullSummary = 5; // match the summary in part $scoreSummaryKeyword = 4; // the exact term matches is found in the content $scoreFullDocument = 4; // match the document in part $scoreDocumentKeyword = 3; // matches a category $scoreCategoryKeyword = 2; // matches the url $scoreUrlKeyword = 1;

Before we get started, there are a few words that do not contribute much to a search that should be removed. Example "in","it","a","the","of" ... . We will filter those out and feel free to add any word you think is irrelevant. Another thing is, we want to limit the length of our query. We don"t want a user to write a novel in the search field and crash our MySQL server.

// Remove unnecessary words from the search term and return them as an array function filterSearchKeys($query)( $query = trim(preg_replace("/(\s+)+/", " ", $query)); $words = array(); // expand this list with your words. $list = array("in","it","a","the","of","or","I","you", "he","me","us","they","she","to","but","that","this","those","then"); $c = 0; foreach(explode(" ", $query) as $key)( if (in_array($key, $list))( continue; ) $words = $key; if ($c >= 15)( break; ) $c++ ; ) return $words; ) // limit words number of characters function limitChars($query, $limit = 200)( return substr($query, 0,$limit); )

Our helper functions can now limit character count and filter useless words. The way we will implement our algorithm is by giving a score every time we find a match. We will match words using the if statement and accumulate points as we match more words. At the end we can use that score to sort our results

Note: I will not be showing how to connect to MySQL database. If you are having problems to efficiently connect to the database I recommend reading this.

Let's give our function a structure first. Note I left placeholders so we can implement sections separately.

Function search($query)( $query = trim($query); if (mb_strlen($query)===0)( // no need for empty search right? return false; ) $query = limitChars($query) ; // Weighing scores $scoreFullTitle = 6; $scoreTitleKeyword = 5; $scoreFullSummary = 5; $scoreSummaryKeyword = 4; $scoreFullDocument = 4; $scoreDocumentKeyword = 3; $scoreCategoryKeyword = 2; $scoreUrlKeyword = 1; $keywords = filterSearchKeys ( $query); $escQuery = DB::escape($query); // see note above to get db object $titleSQL = array(); $sumSQL = array(); $docSQL = array(); $categorySQL = array (); $urlSQL = array(); /** Matching full occurrences PLACE HOLDER **/ /** Matching Keywords PLACE HOLDER **/ $sql = "SELECT p.p_id,p.p_title,p.p_date_published,p. p_url, p.p_summary,p.p_content,p.thumbnail, ((-- Title score ".implode(" + ", $titleSQL).")+ (-- Summary ".implode(" + ", $sumSQL) .")+ (-- document ".implode(" + ", $docSQL).")+ (-- tag/category ".implode(" + ", $categorySQL).")+ (-- url ". implode(" + ", $urlSQL).")) as relevance FROM post p WHERE p.status = "published" HAVING relevance >

In the query, all scores will be summed up as the relevance variable and we can use it to sort the results.

Matching full occurrences

We make sure we have some keywords first then add our query.

If (count($keywords) > 1)( $titleSQL = "if (p_title LIKE "%".$escQuery."%",($scoreFullTitle),0)"; $sumSQL = "if (p_summary LIKE "%" .$escQuery."%",($scoreFullSummary),0)"; $docSQL = "if (p_content LIKE "%".$escQuery."%",($scoreFullDocument),0)"; )

Those are the matches with higher score. If the search term matches an article that contains these, they will have higher chances of appearing on top.

Matching keywords occurrences

We loop through all keywords and check if they match any of the fields. For the category match, I used a sub-query since a post can have multiple categories.

Foreach($keywords as $key)( $titleSQL = "if (p_title LIKE "%.DB::escape($key)."%",($scoreTitleKeyword),0)"; $sumSQL = "if (p_summary LIKE "%".DB::escape($key).."%",($scoreSummaryKeyword),0)"; $docSQL = "if (p_content LIKE "%".DB::escape($key)."% ",($scoreDocumentKeyword),0)"; $urlSQL = "if (p_url LIKE "%".DB::escape($key)."%",($scoreUrlKeyword),0)"; $categorySQL = "if ((SELECT count(category.tag_id) FROM category JOIN post_category ON post_category.tag_id = category.tag_id WHERE post_category.post_id = p.post_id AND category.name = "".DB::escape($key).") > 0,($scoreCategoryKeyword),0)"; )

Also as pointed by a commenter below, we have to make sure that the these variables are not empty arrays or the query will fail.

// Just incase it "s empty, add 0 if (empty($titleSQL))( $titleSQL = 0; ) if (empty($sumSQL))( $sumSQL = 0; ) if (empty($docSQL))( $docSQL = 0; ) if (empty($urlSQL))( $urlSQL = 0; ) if (empty($tagSQL))( $tagSQL = 0; )

At the end the queries are all concatenated and added together to determine the relevance of the post to the search term.

// Remove unnecessary words from the search term and return them as an array function filterSearchKeys($query)( $query = trim(preg_replace("/(\s+)+/", " ", $query)); $words = array(); // expand this list with your words. $list = array("in","it","a","the","of","or","I","you", "he","me","us","they","she","to","but","that","this","those","then"); $c = 0; foreach(explode(" ", $query) as $key)( if (in_array($key, $list))( continue; ) $words = $key; if ($c >= 15)( break; ) $c++ ; ) return $words; ) // limit words number of characters function limitChars($query, $limit = 200)( return substr($query, 0,$limit); ) function search($query)( $query = trim ($query); if (mb_strlen($query)===0)( // no need for empty search right? return false; ) $query = limitChars($query); // Weighing scores $scoreFullTitle = 6; $ scoreTitleKeyword = 5; $scoreFullSummary = 5; $scoreSummaryKeyword = 4; $scoreFullDocument = 4; $scoreDocumentKeyword = 3; $scoreCategoryKeyword = 2; $scoreUrlKeyword = 1; $keywords = filterSearchKeys($query); $escQuery = DB::escape($query); // see note above to get db object $titleSQL = array(); $sumSQL = array(); $docSQL = array(); $categorySQL = array(); $urlSQL = array(); /** Matching full occurrences **/ if (count($keywords) > 1)( $titleSQL = "if (p_title LIKE "%".$escQuery."%",($scoreFullTitle),0)"; $sumSQL = "if (p_summary LIKE "%".$escQuery."%",($scoreFullSummary),0)"; $docSQL = "if (p_content LIKE "%".$escQuery."%",($scoreFullDocument), 0)"; ) /** Matching Keywords **/ foreach($keywords as $key)( $titleSQL = "if (p_title LIKE "%".DB::escape($key)."%",($scoreTitleKeyword ),0)"; $sumSQL = "if (p_summary LIKE "%".DB::escape($key)."%",($scoreSummaryKeyword),0)"; $docSQL = "if (p_content LIKE "% ".DB::escape($key)."%",($scoreDocumentKeyword),0)"; $urlSQL = "if (p_url LIKE "%".DB::escape($key).."%",( $scoreUrlKeyword),0)"; $categorySQL = "if ((SELECT count(category.tag_id) FROM category JOIN post_category ON post_category.tag_id = category.tag_id WHERE post_category.post_id = p.post_id AND category.name = "". DB::escape($key)."") > 0,($scoreCategoryKeyword),0)"; ) // Just incase it"s empty, add 0 if (empty($titleSQL))( $titleSQL = 0; ) if (empty($sumSQL))( $sumSQL = 0; ) if (empty($docSQL))( $docSQL = 0; ) if (empty($urlSQL))( $urlSQL = 0; ) if (empty($tagSQL))( $tagSQL = 0; ) $sql = " SELECT p.p_id,p.p_title,p.p_date_published,p.p_url, p.p_summary,p.p_content,p.thumbnail, ((-- Title score ".implode(" + ", $titleSQL). ")+ (-- Summary ".implode(" + ", $sumSQL).")+ (-- document ".implode(" + ", $docSQL).")+ (-- tag/category ".implode (" + ", $categorySQL).")+ (-- url ".implode(" + ", $urlSQL).")) as relevance FROM post p WHERE p.status = "published" HAVING relevance > 0 ORDER BY relevance DESC,p.page_views DESC LIMIT 25"; $results = DB::query($sql); if (!$results)( return false; ) return $results; )

Now your search.php file can look like this:

$term = isset($_GET["query"])?$_GET["query"]: ""; $search_results = search($term); if (!$search_results) ( echo "No results"; exit; ) // Print page with results here.

We created a simple search algorithm that can handle a fair amount of content. I arbitrarily chose the score for each match, feel free to tweak it to something that works best for you. And there is always room for improvement.

It is a good idea to track the search term coming from your users, this way you can see if most users search for the same thing. If there is a pattern, then you can save them a trip and just cache the results using Memcached.

If you want to see this search algorithm in action, go ahead and try looking for an article on the search box on top of the page. I have added extra features like returning the part where the match was found in the text. Feel free to add features to yours.

Did you like this article? You can subscribe to read more awesome ones. .

On a related note, here are some interesting articles.

It is time to deal with mysql_* functions once and for all. These methods are deprecated and slow. The time to upgrade has long passed yet we still see it everywhere. Since I cannot force every author to update their tutorial and blogs, I decided to write a post to hopefully rank better and provide the essential information to help new comers.

Making your own website shouldn't be too difficult. Hosting companies like Godaddy or Hostgator make it super easy for anyone to get started; they allow you to create a whole website without ever writing code. For most people, it is plenty to run a WordPress blog. If this is what you are looking for you should head to Godaddy.com right now. We are done here. But on the other hand, if you want to have control and not be limited by the short comings of a shared hosting without busting your wallet, you have come to the right place.

Vim is my favorite text editor on the terminal. After playing for a little while with nano and emacs, I finally settled with vim for its simplicity (bare with me please). Although it can be customized and used like an entire IDE, I use it mostly for editing files on my servers and making small but crucial changes. Let's not get into Editor war and get started.

Comments(45)

Zaryel Aug 12 2015:

Ian Mustafa Sep 26 2015:

Rob Sep 29 2015:

adeem Feb 11 2016:

Ivan Venediktov Apr 9 2016.

Of course, everyone who has at least some idea of ​​search engine promotion knows about the meaning of meta tags. Everyone is aware of the importance of title, description, h1-h6, alt and other tags. No one denies that they affect website optimization. But search engines have an ambivalent attitude towards one of the tags - the keywords tag.

In recent years, there has been a heated debate on the Internet that continues to this day: is it worth using the keywords meta tag at all? Unfortunately, no one can still give an exact answer. Let's consider different points of view and try to understand this issue.

What are keywords?

Keywords are keywords (no more than 20 for one page of the site) corresponding to the content of the page.

In the page code this meta tag looks like this:

Initially, the tag had a significant impact on the relevance of the site’s pages, and consequently on the site’s ranking in the top positions of search engines.

Knowing this, the site owners began to cheat - abuse keywords or add a large number of inappropriate words to this tag. And the search engines discovered this quite quickly.

What is happening now?

As they say, from one extreme to another: as a result, search engines stopped attaching any meaning to this tag at all.

Yandex

Yandex representatives stated the following about keywords: “...may be taken into account when determining whether a page is relevant to search queries”.

Please note that the keyword here is Maybe. After all Maybe doesn't mean at all taken into account.

Google

The system leaves no doubts and gives no grounds for thought. Everything is concise and clear here: “We don’t use keywords meta-tag in a search-ranking”, “Google has ignored the keywords meta tag for years and currently we see no need to change that policy”.

“We do not use the keywords meta tag in search rankings”, “Google has ignored the keywords meta tag for many years, and there is currently no need to change this policy”.

Rambler, Yahoo, Mail.ru

They share the opinion of Google and believe that the keywords meta tag has exhausted its usefulness. Therefore, it is not taken into account by these search engines at all.

But why do many people still use keywords?

Most likely, this is due to Yandex’s ambiguous wording about the tag. The logic of site owners is this: if there is hope that Yandex will take the tag into account, and Google, Rambler, Yahoo and Mail.ru are neutral about the meta tag, then filling it out will not make things worse.

What if it does?

There is an opinion among optimizers that filling in the keywords tag can be harmful. If search engines do not consider a tag as a tag, then the text included in it is read as regular site text. And if you have already used these keys in other tags and in the body of the text, then there is a risk of “overspamming” the page with keys. Well, overspam (excessive nausea) can get you under the filter.

1PS point of view

So far we have described the general situation and the different opinions on the issue. Everyone has their own point of view. Our point is that it is better not to fill in the keywords tag. There is definitely no benefit from it, but there is still a risk of falling under the filter.

It’s better to promote your website with the right content and tags