Now, as I mentioned before, I don't' have experience with writing code to deal with zillions of hits, but I think a simple implementation of "find all pages belonging to category1 AND category2" is accomplished using a sql like this pseudocode: "select pages, count(pages) as count where (category='category1' or category='category2') group by pages having count='2'"
Heh, I wonder how you came up with this SQL... it's definitely not the most straightforward way of doing it, and (I'm afraid) also not the most efficient. It feels like it was deliberately trying to be more clever than necessary ;)
You method would: * retrieve all pages from category1 * retrieve all pages from category2 * sort the whole list * group them (i.e. replace runs with their counts) * filter out those where the count is 2
The straightfoward method would be:
select a.page from categorylinks a where a.category='category1' AND EXISTS ( select * from categorylinks b where a.page=b.page AND b.category='category2' )
This would: * retrieve all the pages from category2 * find all the pages in category1 that are any of those
I don't have MySQL handy, but tested this on MSSQL and the second method was way faster. :)
Timwi