[Phpwiki-talk] Unworkable solution to SQL problem

[Steve W. <sw...@wc...>]

Hi Arno,

This is a bit off, but I have been thinking about the problem on
Sourceforge with the query:

select distinct hitcount.pagename, hitcount.hits
from wikilinks, hitcount
where
  (wikilinks.frompage=hitcount.pagename
   and wikilinks.topage='AddingPages')
or
  (wikilinks.topage=hitcount.pagename
   and wikilinks.frompage='AddingPages')
order by hitcount.hits desc, hitcount.pagename;

Which returns that error about the size being too large. It's not a
problem since you set the variable in MySQL, but I've been trying to think
of a way around the Cartisian join, if one indeed occurs.

I was reading the docs for Postgresql this weekend and it supports UNION,
and INTERSECT. I think UNION might have worked in this case, but
unfortunately MySQL doesn't support it. We could have done: 

select topage from wikilinks where frompage='AddingPages'
union
select frompage from wikilinks where topage='AddingPages'

to get all the page names that link to AddingPages.

That could be an inner select, and then we do:

select pagename, hits from hitcount where pagename in (
  select topage from wikilinks where frompage='AddingPages'
  union  
  select frompage from wikilinks where topage='AddingPages'
) order by hits desc, pagename;

I think subselects are more efficient, if the database knows how to
optimize for them.

Just thinking out loud,
sw

...............................ooo0000ooo.................................
   Hear FM quality freeform radio through the Internet: http://wcsb.org/
                     home page: www.wcsb.org/~swain