CakePHP response slow

Question

I have a few set of API's written in CakePHP which we want to migrate to Amazon AWS.

Following is the current situation:

1. Website is hosted on GoDaddy as shared hosting with domain, for example: democompany.com

2. Backend database is MySQL which we access via PhpMyAdmin. It has several tables e.g. users, plans, purchases etc.

3. All API's are written in CakePHP which we access via base URL: democompany.com/cake

For example, for adding an entry in users table, we create a JSON and send it via REST API. Below image show the JSON:

Now, since our users are growing, our API response time has slowed. Sending a POST or GET takes time to return the response.

We were thinking of migrating our API's and database to Amazon AWS or any other solution. I am not much aware of AWS, so don't know which product would be best. Which would be the best solution and offer immediate response and would be cost-effective?

Answer 1

A slowing MySQL database with PHP backend can have many reasons. Try these:

• One of the most important thing is to think about your indexes. You probably have a primary index on ID with auto_increment. But if you query a lot on another column (like SELECT * FROM users WHERE email LIKE '%john%') it is important to also set an index on the email column. How indexes work is vital if you want high performing databases. See this post for a start of how this works: How do MySQL indexes work?

• Another thing is the amount and complexity of your queries. Do you use many queries in one page load or only a few? Try to get as much information as possible out of one query.

• Sorting data can be extremely expensive as well. Does removing SORT BY whatever speed things up a lot? Check this out: MYSQL, very slow order by

• If you looked at all of this and are sure that all your queries are running smooth you can look at persistent connections (re-using connections in one page load for example), bigger servers, etc.