Query is slow as soon as I use one join

Guys,

I hope you can help me out… I’m a little shocked by the speed of my query…

I hope I’m being clear in stating my problem and providing enough information… I’ll be really happy with any pointers to where the problem might be …

When I do a query without joins, for example:

SELECT t1.id, t1.status, t1.filetype, t1.filename, t1.hits, t1.message, t1.title, t1.tags, t1.extension, t1.location, t1.uid, t1.upload, t1.moderationstatus FROM data_mediagallery AS t1 WHERE (t1.status = 3) AND (t1.vhost = 1) GROUP BY t1.id ORDER BY upload DESCLIMIT 50;

The query takes about 0.17s on a table with 50.000 records… Which is fine…

When I do a a little more complex query, however… it becomes slow…

SELECT t1.id, t1.status, t1.filetype, t1.filename, t1.hits, t1.message, t1.title, t1.tags, t1.extension, t1.location, t1.uid, t1.upload, t1.moderationstatus, AVG(if(t2.vote is not null,t2.vote,0)) AS rating, COUNT(t3.time) AS commentcount, t4.user AS user_nameFROM data_mediagallery AS t1 LEFT JOIN data_mediagalleryvotes AS t2 ON t1.id = t2.mid LEFT JOIN data_mediagallerycomments AS t3 ON t1.id = t3.mid LEFT JOIN users AS t4 ON t1.uid = t4.id WHERE (t1.status = 3) AND (t1.vhost = 1) GROUP BY t1.id ORDER BY commentcount DESC LIMIT 50;

Ok, so it is understandable this is a bit slower (more than 2 seconds…) but, if I take any 2 out of 3 joins the difference is minimal…

So what I conclude (but I’m not sure) every time I’m using a join, a different method to lookup the data from the main table is being used… Because it doesn’t matter which join I use, or how many joins with other tables… the speed difference is about the same…

However if I do an explain, this is what I get:

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: ref
possible_keys: vhost_status
key: vhost_status
key_len: 10
ref: const,const
rows: 26206
Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: t2
type: ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: sabretooth2.t1.id
rows: 1
Extra:
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: t3
type: ref
possible_keys: mid
key: mid
key_len: 5
ref: sabretooth2.t1.id
rows: 1
Extra:
*************************** 4. row ***************************
id: 1
select_type: SIMPLE
table: t4
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: sabretooth2.t1.uid
rows: 1
Extra:

The exact same keys and lookup method are being used from my first table as when I do the query without any joins… so that can’t be it…

Then I figured… maybe it has to do with my LIMIT… In the case when there’s a JOIN done, in fact all the rows of the data_mediagallery table are being loaded…

I really hope somebody can help me out… this app is close to going live and could cause a meltdown :wink:

If I can provide more information… I’ll be happy to give anything that might be needed… Any pointers would be great!

by the way… I’m running 5.0.32

You have a faulty GROUP BY syntax since you are selecting columns that are not part of the GROUP BY expression without using group functions.
Which means that you can’t be sure which of the values that you get.
MySQL is basically the only DBMS in the world that allows it.

As a suggestion it is also good if you supply the explain of the query that is fast so that we have something to compare with.

But I’m guessing that the real reason why your joins are slow is probably that your original query are using a combined index on:
(status, vhost, id)
and that means that it can solve both the WHERE clause and the GROUP BY with the same index. Making that query very fast.

But as soon as you join with one other table the GROUP BY can no longer be optimized that way so your DB needs to build a temporary table and sort that.

My suggestion is that you increase your sort_buffer_size server variable.
If the default 2Mb is to small MySQL will instead create a temporary file and sort on that which is much slower than in memory sort.