Improving any video platform search speed with and without Elasticsearch while ensuring fuzzy search, typo tolerance, and fast performance.

The search queries in content.php use two different approaches:

Full-Text Search (MATCH...AGAINST) – This is used for title searches but may not support fuzzy matching or typos well.
LIKE-based Search (title LIKE '%keyword%') – This is slower on large datasets because it scans the entire table.

Both methods are inefficient for large databases with over 100K video entries, causing slow searches.

Alternative Search Optimization Without Elasticsearch

To improve speed and support typo tolerance and fuzzy search, I suggest:

1. Use MySQL FULLTEXT with n-gram and Fuzzy Matching

Modify the title column in T_VIDEOS to use FULLTEXT with IN NATURAL LANGUAGE MODE.
Implement a fuzzy matching function with Levenshtein Distance in PHP to catch typos.

2. Use Indexed Full-Text Search for Speed

Ensure FULLTEXT(title, description, tags) is enabled on T_VIDEOS.
Change MATCH (title) AGAINST ('$keyword') to use BOOLEAN MODE (IN BOOLEAN MODE), which allows more flexible searches.

3. Optimize LIKE Queries

Replace LIKE '%keyword%' with LIKE 'keyword%' where possible (prefix searches are faster).
Add an additional INDEX for title and description.

4. Implement a Fuzzy Search Helper in PHP

Use Levenshtein Distance or Soundex to find similar words for typo handling.

Database Size vs. Performance

Number of Videos	Expected Search Speed
200K - 1M	Fast (~500ms - 1s) with proper indexing
1M - 3M	Moderate (~2ms - 5s) but still usable
3M - 5M+	Slow (~5s - 8s), may require caching
5M+	Struggles (~10s+), alternative storage like Redis or SphinxSearch needed

By default, MySQL FULLTEXT search doesn’t handle long natural language queries like "How to make a cheesecake?" effectively. However, we can make it work similar to Google or YouTube search with these improvements:

1. Natural Language Processing (NLP) Preprocessing

Strip unnecessary words (stop words) like "to", "a", "how".
Convert "How to make a cheesecake?" into:
- "make cheesecake" (core keywords)
- "cheesecake recipe", "cheesecake tutorial" (synonyms)

2. Query Transformation for Better Results

Convert user input into multiple search patterns:
sql
SELECT * FROM videos WHERE MATCH(title, description) AGAINST ('cheesecake tutorial' IN BOOLEAN MODE) OR title LIKE '%cheesecake%' OR title LIKE '%make cheesecake%' LIMIT 20;
This method prioritizes relevant results while keeping fuzzy matching.

3. Fuzzy Search for Typos

If a user types "How to make a cheescak?" (typo in cheesecake), use Levenshtein Distance in PHP to correct spelling.

4. Handling Questions in Search

If a user searches for "How to make a cheesecake?", we:
1. Remove stop words (how, to, a).
2. Convert it into "make cheesecake".
3. Use fuzzy logic for typos.

Here’s a full step-by-step implementation plan to optimize video search in MySQL while supporting fuzzy search, typo tolerance, and natural language queries—without Elasticsearch.

Step 1: Modify Database for Optimized Search

1.1 Add FULLTEXT Index on Videos Table

Open your MySQL database and run:

This enables efficient search using MATCH() AGAINST() instead of slow LIKE '%keyword%'.

1.2 Enable MySQL Boolean Search Mode

Use IN BOOLEAN MODE to allow multi-word searches and better ranking.

1.3 Create a `synonyms` Table (Optional)

Helps handle searches like "How to make a cheesecake?" → "cheesecake recipe".

Insert some synonyms:

This helps expand searches automatically.

Step 2: Preprocess User Input in PHP

2.1 Remove Stop Words (e.g., "how", "to", "a")

Create a PHP function to clean queries:

Example:

2.2 Correct Typos with Levenshtein Distance

Use PHP’s Levenshtein to find close matches for misspelled words:

php

function correctTypo($input, $dictionary) {
    $shortest = -1;
    $closest = $input;
    
    foreach ($dictionary as $word) {
        $lev = levenshtein($input, $word);
        if ($lev == 0) {
            return $word;
        }
        if ($lev < $shortest || $shortest < 0) {
            $closest = $word;
            $shortest = $lev;
        }
    }
    return $closest;
}

// Example usage:
$dictionary = ['cheesecake', 'recipe', 'tutorial', 'make'];
$corrected_word = correctTypo('cheescak', $dictionary); 
// Output: "cheesecake"

Step 3: Optimize Search Query

3.1 Use FULLTEXT Search with Fallback

Modify content.php to use FULLTEXT + LIKE in a hybrid search.

php

$keyword = PT_Secure($_GET['keyword']);
$clean_keyword = cleanQuery($keyword);

// Prepare fuzzy matching (correction)
$dictionary = ['cheesecake', 'recipe', 'tutorial', 'make'];
$corrected_keyword = correctTypo($clean_keyword, $dictionary);

// SQL Query with FULLTEXT + LIKE
$sql = "SELECT * FROM videos 
        WHERE MATCH(title, description, tags) AGAINST ('$corrected_keyword' IN BOOLEAN MODE) 
        OR title LIKE '%$corrected_keyword%' 
        OR description LIKE '%$corrected_keyword%'
        ORDER BY id DESC 
        LIMIT 20";

$get_videos = mysqli_query($db, $sql);

Step 4: Improve Ranking (Smart Search)

4.1 Boost Exact Matches

Modify SQL to prioritize full matches over partial:

Exact match videos appear first.
Most viewed videos rank higher.

Step 5: Cache Frequent Searches

5.1 Use PHP + Redis for Speed

Enable Redis on Your Server:

Store & Retrieve Cached Queries:

This reduces database load by caching frequent searches. ?

Step 6: Implement Auto-Suggestions

Modify ajax/search.php:

This makes real-time search suggestions like YouTube.

If you want faster search performance, Elasticsearch is a great choice because:

✅ Super Fast Search – Faster than MySQL FULLTEXT
✅ Fuzzy Search & Typos Handling – Built-in typo correction
✅ Scalability – Handles millions of videos easily
✅ Advanced Ranking – More accurate search results

Installing Elasticsearch

Below is a simple example of how to install Elasticsearch via Plesk Docker

? How to Install Elasticsearch via Plesk Docker

1️⃣ Go to Docker in Plesk
2️⃣ Search for elasticsearch
3️⃣ Select the latest stable version (e.g.7.9.3 or 8.17.0)
4️⃣ Click "Run", then configure:

Set Port Mapping: 9200 → 9200
Set Memory Limit: 2GB+ (for best performance)
Set Environment Variables:
ini
discovery.type=single-node ES_JAVA_OPTS=-Xms1g -Xmx2g

5️⃣ Start the container, then test if it's running:

Expected response:

Next Steps: Integrate Elasticsearch with Video Search

Now that Elasticsearch is installed, you need to:

Index your videos into Elasticsearch
Modify content.php to query Elasticsearch instead of MySQL
Ensure fuzzy search and typo correction works

Now That Elasticsearch Is Running, Here’s How to Use It for Faster Search

Now that Plesk Elasticsearch x.xx.x is running smoothly, we can replace MySQL search with Elasticsearch, making searches faster, more accurate, and typo-tolerant.

Step 1: Index Your Videos into Elasticsearch

Before we can use Elasticsearch for search, we need to store your videos in Elasticsearch.

Create a New Index for Videos

Run the following cURL command to create an index:

bash

curl -XPUT "http://localhost:9200/videos" -H 'Content-Type: application/json' -d'
{
  "settings": {
    "number_of_shards": 1,
    "number_of_replicas": 0
  },
  "mappings": {
    "properties": {
      "id": { "type": "integer" },
      "title": { "type": "text", "analyzer": "standard" },
      "description": { "type": "text", "analyzer": "standard" },
      "tags": { "type": "text", "analyzer": "standard" },
      "views": { "type": "integer" },
      "published_at": { "type": "date" }
    }
  }
}'

✅ This creates an Elasticsearch index (videos) to store video data.

Step 2: Import Your Existing Videos

Modify sources/search/content.php to insert videos into Elasticsearch.

Add a Function to Index Videos

Find where videos are added to MySQL (INSERT INTO videos).
Add this Elasticsearch indexing function in PHP:

php

function indexVideoInElasticsearch($video) {
    $url = 'http://localhost:9200/videos/_doc/' . $video['id'];
    $data = json_encode($video);
    
    $ch = curl_init($url);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "PUT");
    curl_setopt($ch, CURLOPT_POSTFIELDS, $data);
    curl_setopt($ch, CURLOPT_HTTPHEADER, array('Content-Type: application/json'));

    $response = curl_exec($ch);
    curl_close($ch);
    
    return $response;
}

// Example: Index a video after adding it to MySQL
$video = [
    "id" => 123,
    "title" => "How to Make a Cheesecake?",
    "description" => "Learn how to make a cheesecake step by step!",
    "tags" => "cheesecake, dessert, cooking",
    "views" => 5000,
    "published_at" => "2025-02-10T10:00:00"
];

indexVideoInElasticsearch($video);