help! Join problem after upgrade to mysql version 5

[Free WordPress Themes]

Hi,

I have this SQL statement:

Code:

SELECT DISTINCT ss.id, ss.price
FROM tpl_screenshots4 AS ss, tpl_keywords AS kw
LEFT JOIN tpl_categorie_links AS cat ON ss.id = cat.tpl_id
WHERE ss.id = kw.tpl_id AND cat.category = 76
ORDER BY ss.date_added DESC

this works fine in mysql 4.xx but not in mysql 5

I get this strange error:

#1054 - Unknown column 'ss.id' in 'on clause'

is there something changed in this version what I didn't know?

[Join us @ facebook]

What about this?

Code:

SELECT DISTINCT tpl_screenshots4.id, tpl_screenshots4.price
FROM tpl_screenshots4 , tpl_keywords AS kw
LEFT JOIN tpl_categorie_links AS cat ON tpl_screenshots4.id = cat.tpl_id
WHERE ss.id = kw.tpl_id AND cat.category = 76
ORDER BY tpl_screenshots4.date_added DESC

[Free WordPress Themes]

I don't think that this is the solution, check this:

http://dev.mysql.com/doc/refman/5.0/en/left-join-optimization.html

[Join us @ facebook]

I don't get it. What's the problem?

[Free WordPress Themes]

I don't get it. What's the problem?

mysql 5 has some new rules for joins

[Join us @ facebook]

I guess this is only for LEFT JOIN, as INNER JOIN statements work as before.

Maybe we need to read some theory again

[Free WordPress Themes]

according the manual it should be:

Code:

SELECT DISTINCT ss.id, ss.price
FROM tpl_screenshots4 AS ss
JOIN tpl_keywords AS kw
LEFT JOIN tpl_categorie_links AS cat ON (ss.id = cat.tpl_id)
WHERE ss.id = kw.tpl_id AND cat.category = 64
ORDER BY ss.date_added DESC

and it works (very fast)

I think we need ro learn more about optimization of queries in the new mysql version 

[Join us @ facebook]

I still don't use mysql 5.0 to production servers, but I worked with it for one big project and I was really impressed with how fast is it, especially with big databases.

[Free WordPress Themes]

I still don't use mysql 5.0 to production servers, but I worked with it for one big project and I was really impressed with how fast is it, especially with big databases.

yeah right its really fast, I think I will store the youtube data in the database and stop with the cache files

[Free WordPress Themes]

btw. mysql5 is a little older the future is mysql6

I stopped the development on mysql3 a few month ago

[Guest]

[59.818]

Googlebot