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 
If I can provide more information… I’ll be happy to give anything that might be needed… Any pointers would be great!