The current HashTag System Issues and Suggested Improvements

1. Hashtags Stored as Raw Text Instead of a Relationship

   • The system searches for # in title and description, which is inefficient.
   • Solution: Implement a video_hashtags table to directly associate videos with hashtags.

2. Inefficient Query Performance

   • The current query requires a LIKE search on large text fields, which is slow.
   • Solution: Instead of text search, store hashtag-video relationships in a separate table and use indexed video_id lookups.

3. No Tracking of Hashtag Usage

   • There is no count of how often a hashtag is used.
   • Solution: Add a usage_count column in hashtags and update it whenever a hashtag is added to a video.

4. No Auto-Suggestion for Trending Hashtags

   • The system does not suggest hashtags to users.
   • Solution: Fetch and display trending hashtags based on usage_count.

Full Step-by-Step Implementation Plan for Fixing the Hashtag System

This plan will:

1. Create and modify database tables for an optimized hashtag system.
2. Update PHP functions to store and fetch hashtags efficiently.
3. Modify AJAX files (submit-video.php, ffmpeg-submit.php, etc.) to ensure correct hashtag storage.
4. Fix sources/hashtag/content.php so it correctly fetches videos by hashtags.
5. Ensure the system scales for large databases with 500K+ videos.

Step 1: Create and Modify Database Tables

We will create the video_hashtags table and update the hashtags table.

Step 1.1: Create video_hashtags Table

Run this SQL command in your database:

✅ Now, hashtags will be stored in a separate table instead of the video title and description.

Step 1.2: Modify hashtags Table to Track Usage

Run this SQL to add a usage_count column:

✅ This will track how often a hashtag is used.

Step 1.3: Add Table Definitions to tables.php

Open tables.php and add these lines at the bottom:

✅ Now, all scripts can reference these tables dynamically.

Step 2: Modify PHP Functions to Handle Hashtags

We need to update functions_one.php to correctly:

• Store hashtags from title and description in video_hashtags
• Increase the usage_count in hashtags
• Fetch videos efficiently

Step 2.1: Fix getHashTagId() to Ensure Hashtag Exists

Location: functions_one.php

Find this:

✅ Replace it with this:

✅ Now, hashtags are always stored properly with a usage count.

Step 2.2: Fix addToHashTags() to Store Hashtags in video_hashtags

Find:

✅ Replace it with this:

✅ Now, hashtags are correctly linked to videos without duplicates.

Step 3: Modify AJAX Files for Hashtag Storage

Now, we need to update submit-video.php, ffmpeg-submit.php, and edit-video.php to store hashtags correctly.

Step 3.1: Fix submit-video.php

Find this:

✅ Replace it with this:

✅ Now, hashtags are correctly extracted and stored when a video is uploaded.

Step 4: Fix sources/hashtag/content.php to Fetch Videos

Find this:

✅ Replace it with this:

✅ Now, hashtags are fetched using video_hashtags, not slow LIKE queries.

Final Steps

1️⃣ Create video_hashtags table and modify hashtags.
2️⃣ Update functions_one.php to store hashtags properly.
3️⃣ Modify submit-video.php, ffmpeg-submit.php, and edit-video.php.
4️⃣ Fix sources/hashtag/content.php to fetch videos correctly.
5️⃣ Test by uploading and searching for hashtags.

Optimizing Website Speed & Video Handling with Redis

To speed up, we can optimize video search, metadata fetching, and frequently accessed pages using Redis caching.

1️⃣ What Can We Cache in Redis?

✅ Video Metadata (Title, Views, Thumbnails, etc.)
✅ Search Results (Hashtags, Popular Videos, User Queries)
✅ Homepage Trending Videos
✅ User Profiles

2️⃣ Caching Video Metadata in Redis

Every time a user views a video, instead of fetching details from MySQL every time, cache it in Redis for quick access.

Modify PT_GetVideoByID($video_id) in functions_one.php

Original Code (Slow MySQL Query)

Optimized with Redis

✅ Result

• Faster page loads (No need to query MySQL every time)
• Reduced database load
• Boosted site performance

3️⃣ Speed Up Video Search with Redis

Instead of searching directly in MySQL, store popular hashtag searches in Redis.

Modify content.php for Hashtag Search

Original Code (Slow)

Optimized with Redis

✅ Result

• 5x Faster hashtag search
• Less MySQL load

4️⃣ Speed Up Trending Videos on Homepage

Instead of querying trending videos every page load, cache it for 5 minutes.

Modify home.php

✅ Result

• Homepage loads instantly
• No heavy MySQL queries

5️⃣ How to Clear Cache When Video Updates?

Whenever a user uploads or edits a video, clear the cache.

Modify edit-video.php

Now, your hashtag system is fully optimized and scalable for 10M+ videos!