5
\$\begingroup\$

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

\$\endgroup\$
5
\$\begingroup\$

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.

\$\endgroup\$
  • 2
    \$\begingroup\$ 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. \$\endgroup\$ – 200_success Mar 4 '14 at 2:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.