How to analyze queries to the MySQL database in Linux environment or why can a query be stupid?

Question

PHP

Good day everyone!

There is some corporate portal, raised on Apache + PHP in a Linux CentOS 6 environment.
The site uses jQuery and Foundation frameworks. The portal is so old that some commands in the PHP code are underlined by the PHPStorm code editor and write that it is deprecated. No one will remake it yet, it is planned to introduce Bitrix instead, but this is a separate sadness.

Now the point. Imagine a modal window with three fields and a "Run" button.
The first button is "Region", the second is "Subdivision", the third is "Task Recipient".
When you select any value in the "Region" field, a GET request is sent via AJAX to the server, which receives a list of departments from the MySQL database by region code and returns it. This code is inserted into the second select, which is "Department". With the recipient of the task is similar.
So, between choosing a region and getting a list of departments, a hell of a lot of time passes, about 6 seconds. Another 6 seconds to load the recipients of the task by selecting the "Division" field. Already 12 seconds. Funny, you say? Now imagine that the head of the department needs to distribute 30 tasks among his subordinates in the morning. Represented? So it's 6 minutes, not counting the time it takes to go into each task, click the "Redirect" button and the loading time of the modal window that I write about. Roughly speaking, it is necessary to spend from 10 to 15 and even 20 minutes, depending on the number of tasks, on the distribution of tasks by department.
In short, annoying.
We began to understand and stumbled upon the fact that some request in PHP to the database was stupid. But we can't catch him.

In this regard, the question is: How and how can you monitor database queries and analyze them in Linux?

I'm not the coolest questioner, so I'll answer additional clarifying questions.
Thanks in advance.

Answer 1

We form MySQL slow log, according to its result we do EXPLAIN. We draw conclusions from the result. If anything was possible - we optimized it - we return to the code and start crying for the analysis of the thoughtless noodles written there and optimize