MySQL “Explain”

Posted by: scoopseven 17 years, 3 months ago

This command would have saved me a couple of hours today should I have known about it. While running a query that returned 48k rows, I needed to run a subquery to check against another table to exclude certain other rows. (Querying a list of people to send a promotional email to while trying to make sure the email didn't go to any people who were already registered for a show [show registrants stored in that separate table]). I attempted to do this using several different techniques:

• and m_email not in (select email from attendees where shw_id = 19)
• and NOT EXISTS (SELECT * FROM attendees WHERE m_email = email AND shw_id='19')
• left outer join attendees on email = m_email where email IS NULL

All of these queries would hang.  The queries would lock access of all other queries to our main people table and the hanging query had a status of "Sending". When the same query was run without including the subquery it would never have a status or "sending", only "preparing" and "writing", hence it never hung. Then I simply put the word "Explain" in front of my select query and MySQL nicely broke down, in great detail, exactly how the query was being handled by MySQL server. It was very apparent that the subquery was being run against an un-indexed column. I added an index to that column and viola! Share on Twitter Share on Facebook

• ← Updating CFMX 6.1
• cfhttp / JDK caching →