MySQL Forums
Forum List  »  Performance

Slow explain query
Posted by: Mark
Date: December 06, 2004 09:47PM

I have an application that generates queries on the fly. They are sometimes ridiculously inefficient, and it would often be better to tell the user to try something else than to execute them. I tried having the application issue an EXPLAIN query before the real query in order to identify slow queries and abort. However these EXPLAIN queries are sometimes themselves very slow. The example below took 627 seconds. Any suggestions for how to recognize slow queries fast? The ability to impose a time limit on query processing would be a helpful alternative.

Compounding the problem is that a slow query brings my Win2000 computer to its knees. None of the nice documented ways to stop the query or the server have a high enough priority to interrupt. I end up killing the MySQL process through the task manager. Even this takes minutes, and has occasionally resulted in data corruption. Any suggestions for stopping a runaway query? Lowering the priority of the MySQL process might help, but doing this through the task manager generates a warning that changing process priorities can make Windows crash, and indeed that seems to be a common result.

Thanks for any help!
Mark

Details:

System:
Win2000 5.00.2195 SP4
1.2GHz/1GB P3 Mobile

MySQL:
4.1.7-nt

Example slow query (note that "explain" is part of the query):
EXPLAIN
SELECT Info_UID FROM
(SELECT DISTINCT node_1.* FROM Info node_1, ActionTable node43_2, UserTable node43_3
WHERE ((Select count(node16_1.Info_UID) FROM Info node16_1, ActionTable node16_2
WHERE node16_1.Info_UID = node16_2.Info_UID
AND node43_3.User_UID = node16_2.User_UID) < 13)
AND node_1.Info_UID = node43_2.Info_UID
AND node43_3.User_UID = node43_2.User_UID) node_1

Options: ReplyQuote


Subject
Views
Written By
Posted
Slow explain query
4808
December 06, 2004 09:47PM
2245
December 10, 2004 09:14AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.