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\$
0

1 Answer 1

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\$
1
  • 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\$ Commented Mar 4, 2014 at 2:50

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.