Search code examples
mysqldatabaselaravelperformancemyisam

Slow query at one DB, but fast at his copy

We have a Laravel application (version 5.4.18) which is connected to MySQL (5.6.38) database (300k rows totally), MyISAM type. And we have api-response like Model::with('anothermodel')->paginate(25), and at some point the execution time of this query has reached 18 seconds, which is very big value. I created a sandbox with a copy of the live laravel environment that uses a copy of the exaclty same database (at the same server), and now this api-response is executed in 2,5 seconds. If we trying to connect sandbox laravel to the live DB, then api execution is 18s again. Please check the image.

api demo

So if we assume that the problem is in the DB table of live website, but if we try to measure this query with microtime, then it will show that time required to generate this query and grab the data from database is just 0.7 sec.

So if we assume that problem is in API routes or in the Laravel code, but exaclty same code is executed without any problem if I just copy it into the subdirectory (as a sandbox).

Any ideas?

Also some server info: PHP 7.0.29 at Linux server with 32gb RAM.

Solution

  • I have 3 solutions for you:

    1) check & optimize & repair the table using this command:

    mysqlcheck -u root -p --auto-repair --check --optimize --all-databases

    2) if problem not solved check your query execution plan using monitoring apps like Newrelic

    3) Sometimes it's not about specific table it's about database corruption, try to drop and create a new one with old data. How to recover/recreate mysql's default 'mysql' database