Re: [Phpwiki-talk] linking scheme and markup

[Reini U. <ru...@x-...>]

Jeff Dairiki schrieb:
> 
> On Wed, 07 Nov 2001 13:19:24 +0000
> "Reini Urban" <ru...@x-...> wrote:
> 
> > mysql 3.22.30 is too old. we are at 3.23.x
> > remove USING(id) as Jeff sent you.
> 
> Though I don't think there's any fundamental reason we can't or
> shouldn't support mysql 3.22.30.
> 
> Tara/Steph: if you figure out anything, do let me know...
> 
> My current hunch is that the older mysql has a problem
> specifically with the INNER JOIN ... USING(...) syntax
> and that "LEFT JOIN ... USING(...)" would work fine.
> I'd like some confirmation of that, though, before I act
> on it.

mysql 3.22.25:

[ok] SELECT topage, count(*) AS nlinks FROM wikilinks LEFT JOIN wiki AS dest
ON topage=dest.pagename WHERE dest.pagename IS NULL GROUP BY topage ORDER BY
nlinks DESC;

[ok] select distinct pagename from wiki left join wikilinks on pagename=topage
where topage is NULL order by pagename;
(but aching slow! 13 sec on 500/3200 pages/links)

[ok] select w.pagename,s.score from wiki as w left join wikiscore s on
w.pagename=s.pagename;

[ok] select w.pagename,s.score from wiki as w left join wikiscore s using
(pagename);

fail:
mysql> SELECT topage, count(*) AS nlinks FROM wikilinks inner JOIN wiki AS
dest ON topage=dest.pagename WHERE dest.pagename IS NULL GROUP BY topage ORDER
BY nlinks DESC;
ERROR 1064: You have an error in your SQL syntax near 'ON topage=dest.pagename
WHERE dest.pagename IS NULL GROUP BY topage ORDER BY nli' at line 1

mysql> select w.pagename,s.score from wiki as w inner join wikiscore s using
(pagename);
ERROR 1064: You have an error in your SQL syntax near 'inner join wikiscore s
using (pagename)' at line 1

mysql> select w.pagename,s.score from wiki as w join wikiscore s using
(pagename);
ERROR 1064: You have an error in your SQL syntax near 'using (pagename)' at
line 1

mysql> select w.pagename,s.score from wiki as w outer join wikiscore s using
(pagename);
ERROR 1064: You have an error in your SQL syntax near 'outer join wikiscore s
using (pagename)' at line 1

I see no advantage in JOIN ... USING, besides shorter strings. 
but left join ... using is okay.
 
> Perhaps, for maximum portability, we/I should convert all
> the joins to plain cross joins.  Instead of
>   SELECT FROM x INNER JOIN y USING(id) WHERE ...
> use the archaic:
>   SELECT FROM x,y WHERE x.id=y.id AND ...

Agreed. There's no speed improvement in USING, but a portability problem.
In another big php project of mine which still supports php3, we use 
           SELECT FROM x,y WHERE x.id=y.id ...
and better SELECT FROM x LEFT JOIN y ON x.id = y.id WHERE ...
http://www.theexchangeproject.org
-- 
Reini Urban
http://xarch.tu-graz.ac.at/home/rurban/