Alternatives for Mysql Full Text Search with PHP

Yuri Boyz
Yuri Boyz used Ask the Experts™
on
I am using a PHP based CMS which stores my content Pages in the MySQL Database. I have hundred's of CMS pages and it is growing. Now I want to add search feature in my website.
Instead of using MYSQL Full Text search  what are the alternative options which are easy to implement and the search results should be fast and relevant.

Note: When user search any keyword  I want to display the CMS page Title and some content in the search result page. And after user clicks on the page title, user will be redirected to that CMS page.

Looking forward for suggestions.


Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
There are several alternatives but they're probably not "easy" to implement by new users. Most of them require separate indexing. For example, Sphinx can provide nearly instant results across very large document repositories via a MySQL-like interface (it emulates a separate MySQL server). However, it's a separate process to add that content to the index and the indexes usually take up a lot of space.

So there's some learning involved and some extra process but it's all about the near-instant results.

Another popular open source one is Solr.
Yuri BoyzSoftware Engineer

Author

Commented:
Yes I have also read about Sphinx and I also working in PHP. Unfortunately I did not find any step-by-step tutorial to implement it for my PHP application. There are several tutorials but they are relatively different according to my requirement.

Can anyone provide some easy integration link which helps me to integrate it in my PHP based CMS website.  

Note: When user search any keyword  I want to display the CMS page Title and some content of CMS page in the search result page which is stored in MySQL database.
nociSoftware Engineer
Distinguished Expert 2018

Commented:
What is needed is a database table where you store for EACH word you have (except for some in a list, like 'and', 'or',  and other blacklisted words). keep a reference where that work occurs. (table, field, possibly possition in the field)
then when you want to search for a word you can quickly determine if it is there or not, if it is present, you will also have all references needed to persui the real content.
If the word list gets too big, you need to break that down into a letter list.  combined of a letter/subset as key
Next is from "game theory' described by Claude Shannon as an approach to play scrabble.
you start searching for (null,firstletter) in the table and get a a reference1 to nextletters... then search for (reference1,2nd letter) and get a reference2 for third letter, then search for (reference2,3rd letter)... until you reach a node that mark it is a hit... on that hit not the table/field/offset reference could exist.

Home growing those will become a drag in the first case, end definitly not simple in the second case.
Oh and with each update you will need to update the databases of keywords as well.... (either removing old contents in case of replacement, as well as insert new data).
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Yuri BoyzSoftware Engineer

Author

Commented:
Currently  the main question is whether I use Sphinx for my website or not? I have mentioned the whole scenario in above question.
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Have you tried Elastic Search (based on Apache Lucene?)
https://www.elastic.co/guide/index.html

A comparison of ElasticSearch and Sphinx here

https://db-engines.com/en/system/Elasticsearch%3BSphinx

Here is a GitHUB project for low level client for PHP (ElasticSearch)
https://github.com/elastic/elasticsearch-php

ElasticSearch (after indexing your data) provides an API based approach for returning data to your application. You can read more about that here

https://www.elastic.co/guide/en/elasticsearch/guide/current/search-lite.html

This Sitepoint article gives a tutorial on using ElasticSearch with PHP

https://www.sitepoint.com/introduction-to-elasticsearch-in-php/
Yuri BoyzSoftware Engineer

Author

Commented:
@Julian: Thanks for sharing this. I have also researched on Elastic but from integration point of view I think Sphinx is more easy to integrate with CMS like Drupal.
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
A brief search returned the following results
https://www.drupal.org/project/elastic_search
The elastic_search module attempts to put elastic search paradigms at the heart of drupal searching. Although [Search API](https://www.drupal.org/project/search_api) is great
it is heavily built around Solr concepts and as such it does not translate directly to the way elastic search works. This is an attempt to bring elastic search ideas directly in to drupal
https://www.drupal.org/project/elasticsearch_connector
Elasticsearch Connector is a set of modules designed to build a full Elasticsearch eco system in Drupal.
It is using the official Elasticsearch PHP library. You can also view the github repo.
One of the biggest companies are using Elasticsearch for big data - check this out.
The ambitions of this module is to help Drupal handle big data at any kind and make it scalable.
Yuri BoyzSoftware Engineer

Author

Commented:
Thanks for the  useful information. Let me review it more deeply and I will get back to you.
Elasticsearch is great for full text search. You can easily connect it with your MySQL database and it will retrieve the data quickly from the database, hence providing a fast search experience to users. Elasticsearch performs deep search in the document. It also finds similar text from web pages, like search a text on blogs. To get this feature on your website, you will have to integrate Elasticsearch in PHP website you have.

Commented:
@Oliver - Julian already mentioned Elasticsearch

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial