CODEWITHSUNDEEP
mysqlsqlperformance
Chris Mccabe
Chris Mccabe

Reputation: 1951

join performance

I'm trying to optimise my queries to make the website load faster

SELECT ac.id as id, ac.first_name, ac.last_name, ac.email, ac.company_name, upd8r_twitter_accts.id as twitter, upd8r_facebook_accts.id as facebook 
FROM upd8r_user_accts ac  
LEFT OUTER JOIN upd8r_twitter_accts ON ac.id = upd8r_twitter_accts.user_id 
LEFT OUTER JOIN upd8r_facebook_accts ON ac.id = upd8r_facebook_accts.user_id 
WHERE ac.`rfid` = '' AND ac.last_name != '' AND ac.`owner_id` = '114' 
ORDER BY ac.`last_name` asc

without the joins the query runs in 0.0001 seconds but with the joins it runs 0.3432 seconds..

How do i go about speeding this up?

Upvotes: 1

Views: 107

Answers (1)

Quassnoi
Quassnoi

Reputation: 425261

SELECT  ac.id as id, ac.first_name, ac.last_name, ac.email, ac.company_name,
        (
        SELECT  id
        FROM    upd8r_twitter_accts
        WHERE   upd8r_twitter_accts.user_id = ac.id
        ORDER BY
                id
        LIMIT 1
        ) twitter,
        (
        SELECT  id
        FROM    upd8r_facebook_accts
        WHERE   upd8r_facebook_accts.user_id = ac.id
        ORDER BY
                id
        LIMIT 1
        ) facebook
FROM    upd8r_user_accts ac
WHERE   ac.`rfid` = ''
        AND ac.`owner_id` = '114'
        AND ac.last_name > ''
ORDER BY
        ac.`last_name` asc

Create the following indexes:

upd8r_user_accts (rfid, owner_id, last_name)
upd8r_twitter_accts (user_id, id)
upd8r_facebook_accts (user_id, id)

Upvotes: 1

Related Questions