Timwi wrote:
... I thought I had mentioned before (maybe not to you) that this query is unnecessarily inefficient (because it scans both categories completely). Why do you use this adventurous approach when there is a much simpler and more obvious one, which also happens to be faster?
SELECT c.cl_from FROM categorylinks c WHERE c.cl_from IN ( SELECT c2.cl_from FROM categorylinks c2 WHERE c2.cl_to='Fantasy_films' ) AND c.cl_to='Disney_films'
Showing rows 0 - 8 (9 total, Query took 0.9231 sec) <-- took longer than the count/group by query SQL query: SELECT c.cl_from FROM categorylinks c WHERE c.cl_from IN (
SELECT c2.cl_from FROM categorylinks c2 WHERE c2.cl_to = 'Fantasy_films' ) AND c.cl_to = 'Disney_films' LIMIT 0 , 30
I tried your way before, an a smaller data set, and got basically identical results for your way and mine. Trying your way on the larger dataset (copy of en), it appears it may be slower than mine. I think I recall that when you tested it, it was in MSSQL, and not MySQL. If the efficiency is equal, I like this approach becuase it scales seasily to intersections of more than two cateogories. It looks like the count/group by approach may be faster in MySQL, based on this example.
Aerik