Wikipedia talk:Bots/Requests for approval/DASHBot 10

explain
 SELECT count(*)
    FROM image, page AS ip 
    JOIN categorylinks ON ip.page_id = cl_from AND cl_to = 'All_non-free_media' AND NOT EXISTS(SELECT * FROM categorylinks WHERE cl_from=ip.page_id AND cl_to = 'All_orphaned_non-free_use_Wikipedia_files')
    LEFT JOIN imagelinks ON il_to = ip.page_title 
    WHERE page_namespace = 6 AND isnull(il_from) AND img_name = ip.page_title AND img_timestamp <= DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 48 HOUR),'%Y%m%d%H%i%s');

This one's 3x faster

explain
select count(*) from
  (select img_name from image where 
     not exists (select 1 from imagelinks where il_to=img_name)
     AND img_timestamp <= DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 48 HOUR),'%Y%m%d%H%i%s')
     ) unused_img
  JOIN page ON img_name=page_title AND page_namespace=6
  JOIN categorylinks ON page_id=cl_from
where
  cl_to = 'All_non-free_media' 
  AND NOT EXISTS(SELECT * FROM categorylinks WHERE cl_from=page_id AND cl_to = 'All_orphaned_non-free_use_Wikipedia_files')
;

Start a discussion about improving the Wikipedia:Bots/Requests for approval/DASHBot 10 page

Talk pages are where people discuss how to make content on Wikipedia the best that it can be. You can use this page to start a discussion with others about how to improve the "Wikipedia:Bots/Requests for approval/DASHBot 10" page.

Start a discussion