Optimize queries from MySQL SP/Triggers/Functions

Question

I have to investigate a MySQL Production server,While investigating I found a DELETE query in "SHOW PROCESSLIST" which was taking more than 400 second in preparing state, I tried to find the query in slow log but i was unable to find the query in slow log.

mysql> show full processlist;
+------+-----------------+---------------------+-----------+---------+------+-----------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+-----------------+---------------------+-----------+---------+------+-----------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 2 | event_scheduler | localhost | NULL | Daemon | 204 | Waiting for next activation | NULL | 
| 7229 | root | 192.168.1.178 | mydb | Connect | 204 | preparing | DELETE
 FROM TEST_DATA_1
 WHERE ID in
 (SELECT ID
 FROM TEST_DATA_2
 WHERE STATE >= 16384
 AND (MODIFY_DT IS NULL or MODIFY_DT <= ADDDATE(SYSDATE(), INTERVAL -10 MINUTE))) | 
+------+-----------------+---------------------+-----------+---------+------+-----------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

I converted this into corresponding SELECT and executed. It took around 180 secs to execute and I observed that It was in preparing state for a long.

I converted the same SELECT sub query to JOIN it executed in fraction of second. So I think I can optimize the query.

After some analysis I found that the query was written inside a procedure, So my question is If a query is written in MySQL SP/Triggers/Functions/Events is slow, it will not be logged into slow log file.

Do we need to optimize each SP/Triggers/Functions/Events individually ? I have around 300+ SP's , 40+ Triggers as well as some events so I need to optimize them independently.

UPDATE ON 31 Dec,2013

1. If a procedure is taking more time than slow log time it should be logged into slow log as CALL ProcedureName() , But this is not happening there is no call statement in slow log why ?

2. Why the query is spending a huge amount of time in preparing state ? From MySQL Doc General Thread States I found the definition of preparing state stated as

preparing

This state occurs during query optimization.

I hope I can rewrite delete query as

DELETE A.* FROM 
TEST_DATA_1 A 
INNER JOIN 
TEST_DATA_2 B ON A.ID = B.ID 
WHERE B.STATE >= 16384 AND (B.MODIFY_DT IS NULL or B.MODIFY_DT <= ADDDATE(SYSDATE(), INTERVAL -10 MINUTE)) 

Is it OK ?

Answer 1

You have actually asked two questions:

1. "If a query is written in MySQL SP/Triggers/Functions/Events is slow, it will not be logged into slow log file"

   Answer: it will not be logged in standard MySQL server. Do take a look at the Percona Server extension for the slow log: in particular look at the log_slow_sp_statements configuration variable. It does what you want: the slow queries within routines are logged. Note that this does not apply to triggers.

2. "Do we need to optimize each SP/Triggers/Functions/Events individually?"

   Answer: well, of course. Each and every query needs to be as optimized as you can, depending on your requirements. A routine is not a "magic solution" that causes inner queries to run faster...

Answer 2

You will some due diligence to go through. In other words, bite the bullet and optimize your queries.

In your particular case, there are two things you should do with that delete query:

REFACTOR THE QUERY AS A DELETE JOIN

Instead of

DELETE FROM TEST_DATA_1 WHERE ID in
(
 SELECT ID FROM TEST_DATA_2 WHERE STATE >= 16384 AND
 (MODIFY_DT IS NULL or MODIFY_DT <= ADDDATE(SYSDATE(), INTERVAL -10 MINUTE))
);

rewrite it as the following

DELETE A.* FROM TEST_DATA_1 A INNER JOIN
(SELECT ID FROM TEST_DATA_2 WHERE STATE >= 16384 AND
(MODIFY_DT IS NULL or MODIFY_DT <= ADDDATE(SYSDATE(), INTERVAL -10 MINUTE))) B
USING (ID);

ADD AN INDEX TO SUPPORT NEW QUERY

Add an index that carries STATE, MODIFY_DT, and ID

ALTER TABLE TEST_DATA_2 ADD IDNEX State_ModifyDate_Index (STATE,MODIFY_DT,ID);

Give it a Try !!!

• Abdul Manaf: I hope I can also write DELETE Query as DELETE A.* FROM TEST_DATA_1 A INNER JOIN TEST_DATA_2 B ON A.ID = B.ID WHERE B.STATE >= 16384 AND (B.MODIFY_DT IS NULL or B.MODIFY_DT <= ADDDATE(SYSDATE(), INTERVAL -10 MINUTE)) Is it OK ?
• Abdul Manaf: I have also updated my question please have a look.