How to speed up a PHP and MySQL directory website on a Droplet for UAE visitors?

Question

I run a business directory website with around 25,000 listings, built on PHP and MySQL. Most of my visitors are from the UAE. The site is hosted on an Ubuntu Droplet with Nginx.

The category and search pages load slowly when the database grows. I have already enabled gzip compression, but pages still take a few second. What should I look at first: MySQL indexing, Nginx caching, a CDN, or a bigger Droplet? Which Droplet region gives the best latency for visitors in the UAE?

Bobby · Answer

Hi there, Start with MySQL. With 25,000 listings, slow category and search pages almost always mean missing indexes or slow search queries. A bigger Droplet or a CDN won’t fix a slow query, it only hides it for a while. 1. Find the slow queries Turn on the slow query log in /etc/mysql/mysql.conf.d/mysqld.cnf: slow_query_log = 1 slow_query_log_file = /var/log/mysql/slow.log long_query_time = 0.5 Restart MySQL, load a few category and search pages, then check the log. Run EXPLAIN on the slow ones: EXPLAIN SELECT * FROM listings WHERE category_id = 12 ORDER BY created_at DESC LIMIT 20; If you see type: ALL or Using filesort, add an index that matches the filter and the sort: CREATE INDEX idx_category_created ON listings (category_id, created_at); 2. Fix the search LIKE '%keyword%' can’t use an index and scans every row. Use a FULLTEXT index instead: ALTER TABLE listings ADD FULLTEXT idx_search (name, description); SELECT * FROM listings WHERE MATCH(name, description) AGAINST('dubai restaurant'); Also check for OFFSET with large page numbers. Page 500 of a category still reads all rows before it. 3. Then cache Make sure PHP OPcache is on. For category pages, which don’t change every second, use Nginx FastCGI cache with a short time like 5 to 10 minutes, or cache query results in Redis or Valkey. 4. Region and CDN DigitalOcean has no region in the Middle East right now. Bangalore (BLR1) and Frankfurt (FRA1) are usually the closest options for the UAE. Test both from your visitors’ location before you move anything. Put Cloudflare in front for static files, since it has edge locations in the UAE. 5. Bigger Droplet last Check htop during traffic. If CPU and RAM are fine, a bigger Droplet won’t help. If MySQL uses most of the RAM, raise innodb_buffer_pool_size before you resize.

Become a contributor for community

DigitalOcean Documentation

Resources for startups and AI-native businesses

The developer cloud

Start building today

Report this

How to speed up a PHP and MySQL directory website on a Droplet for UAE visitors?

Related questions

Why x-ratelimit-remaining-tokens-per-day shows a tenth of your daily limit on DigitalOcean Inference

Comparing model latency and cost on DigitalOcean Inference before you commit to one

Tier 2 serverless inference: Daily token bucket caps at 500K

Tier 2 serverless inference: Daily token bucket caps at 500K

Why x-ratelimit-limit-tokens-per-day too little?

What are the best AI frameworks and tools for adding AI features to mobile apps?

Report this

How to speed up a PHP and MySQL directory website on a Droplet for UAE visitors?

Related questions

Why x-ratelimit-remaining-tokens-per-day shows a tenth of your daily limit on DigitalOcean Inference

Comparing model latency and cost on DigitalOcean Inference before you commit to one

Tier 2 serverless inference: Daily token bucket caps at 500K

Tier 2 serverless inference: Daily token bucket caps at 500K

Why x-ratelimit-limit-tokens-per-day too little?

What are the best AI frameworks and tools for adding AI features to mobile apps?