Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

hi friends can anyone tell me what is the issue with my query. when i execute, it lock the Database for 5 to 10 minutes, please help me to optimize this query .

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;
share|improve this question

closed as unclear what you're asking by Max Vernon, Paul White, Mark Storey-Smith, RolandoMySQLDBA, Michael - sqlbot Mar 7 at 21:47

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the question.

1 Answer 1

up vote 0 down vote accepted
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
        LEFT JOIN goal_sheets AS gs ON u.id = gs.user_id
        LEFT JOIN sheet_user_popup_not_adam AS supna ON u.id = supna.user_id
    where u.registration_page='Chris_Cleanse' and
        s.expired=TRUE and
        gs.user_id IS NULL and
        supna.user_id IS NULL
    ORDER BY u.id DESC

You can transform the subqueries into left joins and check for IS NULL in where conditions. MySQL performs better on joins than on subqueries.

Have you set appropriate indexes on your tables? Otherwise this should be done too. (Indexes on subscriptions.user_id, profiles.user_id, goal_sheets.user_id and sheet_user_popup_not_adam.user_id should exist as minimum.)

share|improve this answer
    
thanks it works for me . –  haseeb Mar 4 at 7:43

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