Laravel Website - Image Tags Optimization
Budget: $30 – $250 USD
I’m running a Laravel + MariaDB website (stock photo platform), and we're experiencing high CPU usage and slow query times due to the way image tags are stored and queried.
Current Problem:
The images table has a column tags that stores comma-separated strings (e.g., "white,jasmine,flower,dark").
We’re using LIKE '%keyword%' to search tags, which causes full table scans.
This is extremely slow on a table with 500,000 + rows and spikes CPU usage to 70–100%
What I Need You to Do:
Normalize the tags column:
Create a new table image_tags with fields image_id and tag, properly indexed.
Migrate all existing comma-separated tags from images.tags into this table (one row per tag per image).
Update the tag search logic:
Replace all queries using LIKE '%tag%' on images.tags with optimized JOIN queries on image_tags.tag.
Add Indexes:
Add indexes to image_tags.tag and image_tags.image_id to speed up joins.
Add index to images.status if not already present.
(Optional) Add Caching:
Use Laravel’s Redis or Cache system to cache popular tag-based queries for faster performance.
Current Problem:
The images table has a column tags that stores comma-separated strings (e.g., "white,jasmine,flower,dark").
We’re using LIKE '%keyword%' to search tags, which causes full table scans.
This is extremely slow on a table with 500,000 + rows and spikes CPU usage to 70–100%
What I Need You to Do:
Normalize the tags column:
Create a new table image_tags with fields image_id and tag, properly indexed.
Migrate all existing comma-separated tags from images.tags into this table (one row per tag per image).
Update the tag search logic:
Replace all queries using LIKE '%tag%' on images.tags with optimized JOIN queries on image_tags.tag.
Add Indexes:
Add indexes to image_tags.tag and image_tags.image_id to speed up joins.
Add index to images.status if not already present.
(Optional) Add Caching:
Use Laravel’s Redis or Cache system to cache popular tag-based queries for faster performance.