Hello fellow developers,

I'm reaching out for some advice on scaling my golf related niche search engine, which is built using PHP and MySQL. Initially, the setup worked beautifully, but as the database has grown to over 3 GB, I'm starting to experience performance issues.

The problem is that the database is not always responsive, especially when making more than 20 queries per minute. I've optimized my queries as much as possible, using indexes, caching, and efficient query structures, but the issues persist.

Here are some details about my current setup:

Server: Virtual Private Server (VPS) with 2 CPU cores, 4 GB RAM, and a 100 GB SSD
Database: MySQL 8.0 with InnoDB storage engine
PHP: Version 8.1 with APCu caching
Queries: Mostly SELECT statements with JOINs and ORDER BY clauses
I'm considering a few options to address the performance issues:

Upgrade server resources: Adding more CPU cores, RAM, and storage to the VPS might help improve database performance. However, I'm not sure if this will be enough to handle the growing database size and query load.

Switch to a different database: I've heard that databases like PostgreSQL, MongoDB, or Elasticsearch might be more suitable for large-scale search engines. However, I'm not sure if the benefits would outweigh the costs of migrating to a new database.

Optimize database schema: Perhaps there are ways to optimize the database schema to improve performance, such as partitioning tables, using full-text indexing, or implementing a message queue.

I'd love to hear from others who have experience with scaling search engines or large databases. What are your recommendations? Should I upgrade server resources, switch to a different database, or explore other optimization strategies?

Some specific questions I have:

What are the pros and cons of switching to a different database, such as PostgreSQL or MongoDB?

How can I determine if my database schema is optimized for performance?

Are there any caching strategies or query optimization techniques that I might be missing?

What are the best practices for scaling a search engine to handle a large volume of queries?

Thanks in advance for your advice and insights!

Best regards,

Mark Putter