View Full Version : joins not helping

04-09-2011, 06:17 AM
i have this query and have tried changing it to left join and it goes from taking 1.68 seconds to 2.73 seconds....

SELECT a.id,a.gender,a.last_activity,a.photo_url,a.subscription,a.username,a.orientation,a.city,a.state,a.z ipcode,a.country,b.status FROM `accounts` a, `profiles` b WHERE b.account_id = a.id AND b.status != '##private##' ORDER BY a.photo_url DESC, a.username ASC LIMIT 0,40

using left join

SELECT a.id,a.gender,a.last_activity,a.photo_url,a.subscription,a.username,a.orientation,a.city,a.state,a.z ipcode,a.country,b.status FROM `accounts` a LEFT JOIN `profiles` b ON b.account_id = a.id AND b.status != '##private##' ORDER BY a.photo_url DESC, a.username ASC LIMIT 0,40

how can i optimize this query? there are 165,000 (give or take a few) rows in both tables

Old Pedant
04-09-2011, 06:30 AM
First, help us help you by making your code readable:

SELECT a.id,a.gender,a.last_activity,a.photo_url,a.subscription,a.username,
FROM `accounts` a LEFT JOIN `profiles` b
ON b.account_id = a.id AND b.status != '##private##'
ORDER BY a.photo_url DESC, a.username ASC
LIMIT 0,40

Offhand, I'd say the only thing you can do (if you haven't done it already) is make sure that all the fields mentioned in your JOIN condition are indexed.

I would assume the accounts.id is your primary key in that table?

So did you index profiles.account_id and profiles.status?

You might consider creating a single composite index for those two fields. Probably more efficient than two separate indexes.

04-09-2011, 06:40 AM
yes they are indexes.
i also just tried moving `status` to the `accounts` table and getting rid of the join altogether. it still is quite slow (0.8 seconds) for only returning 40 rows... im not sure why

SELECT id,gender,last_activity,photo_url,
subscription,username,orientation,city,state,zipcode,country,status FROM `accounts`
WHERE status != '##private##' ORDER BY photo_url DESC, username ASC LIMIT 0,40

Old Pedant
04-09-2011, 07:10 AM
Because it has to *FIRST* find *ALL* the records that match the WHERE clause (or ON for the JOIN), *THEN* it has to SORT all those records...by *TWO* fields. Only then can it choose the first 40 of those records.

In other words, the time to get 40 records and the time to get, say, 200 records is going to be essentially identical if your WHERE/ON causes it to find, say, 10000 records.

Do this query and tell me what you get:

FROM `accounts` a LEFT JOIN `profiles` b
ON b.account_id = a.id AND b.status != '##private##'

04-09-2011, 07:11 AM
that still took 1.04 seconds without ORDER BY or LIMIT

04-09-2011, 07:15 AM
actually i already moved status to accounts table so im not longer looking for a solution for joins... im trying to figure out why this query is so darned slow. it's not like theres millions of rows in the table, it's making my pages load really slowly.

Old Pedant
04-10-2011, 04:17 AM
Same question. What is the NUMBER you get for the COUNT in this query:

FROM `accounts`
WHERE status != '##private##'

Not how long does that take. What's the COUNT?

Though if just the COUNT(*) indeed took 1.04 seconds without ORDER BY or LIMIT, I think you have your answer. You are simply getting so many records that, when you add in the ORDER BY, indeed that's how long it's going to take. The LIMIT probably has nearly no effect on the time (well...if you didn't have it there the time would be *MUCH* longer, just because MySQL has to transfer that much data from the server to the client...but that's all data transfer time...the actual query time is likely almost unaffected by LIMIT).