Query performance of a SELECT statement using WHERE and NOT IN

Question

Can anyone help improve the performance of my query? When I run it, it locks the database for 5 to 10 minutes.

SELECT 
 u.username, 
 u.email, 
 u.created_at, 
 p.firstname, 
 p.lastname, 
 p.address, 
 p.address_2, 
 p.city, 
 p.country, 
 p.state, 
 p.zip, 
 p.phone, 
 p.phone_2, 
 u.last_ip, 
 u.last_login_at, 
 u.auto_login, 
 u.registration_page,
 s.product_name
 FROM
 users AS u
 Left Join subscriptions AS s ON u.id = s.user_id
 Left Join profiles AS p ON u.id = p.user_id
 where u.registration_page='Chris_Cleanse' and
 u.id not in (select user_id from goal_sheets) and 
 u.id not in(select user_id from sheet_user_popup_not_adam) and 
 s.expired=TRUE ORDER BY u.id DESC;

Here is the output of EXPLAIN SELECT: enter image description here

Answer 1

I don't see anything unreasonable about the query itself.

However, if I'm interpreting the output of EXPLAIN correctly, the anti-join with sheet_user_popup_not_adam is being done using a full table scan. Run

SHOW INDEXES FROM sheet_user_popup_not_adam;

If no index exists on the user_id column, then run

CREATE INDEX sheet_user_popup_not_adam_user_id ON sheet_user_popup_not_adam (user_id);

Maybe that should be CREATE UNIQUE INDEX ... instead, if appropriate. Hopefully the performance should improve after you create the index.

• 200_success: On further thought, could you post the output of SHOW INDEXES for all of the tables in this query? In particular, I'm curious about SHOW INDEXES FROM subscriptions, and why the query planner acts as it does.