We have a Mysql 5 server, around 50 users work with it. In specific occasions a user would do a certain type of consultation that is too heavy and the whole system slows down. I have checked the state of the server during this particular type of SELECT consultation and the rest of the open connections in the server go to status Locked.
Being a select consultation, which does not modify anything, I know that there is no need to block tables.
My first question is: What can I do so that the remaining connections wont report as locked?
Second question: How can I set different levels of priority in the “SELECT” consultations, that is, this heavy consultation which takes several minutes to resolve, we don’t mind about taking twice the amount of time, or even three, given that this type of consultation is quite sporadic, however it does bother the fact that the system slows down.
Thank you