Optimize queries from MySQL SP/Triggers/Functions 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&gt; 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 &gt;= 16384
                    AND (MODIFY_DT IS NULL or MODIFY_DT &lt;= 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


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 ?
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 &gt;= 16384 AND (B.MODIFY_DT IS NULL or B.MODIFY_DT &lt;= ADDDATE(SYSDATE(), INTERVAL -10 MINUTE)) 


Is it OK ?
You have actually asked two questions:


"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.
"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...