راهنما:استخراج آمار از پایگاه داده مدیاویکی - ویکی‌پدیا، دانشنامهٔ آزاد

USE fawiki_p; 

USE enwiki_p; 

USE commonswiki_p; 

USE fawiktionary_p; 

select page_title from page join revision on page_id = rev_page  where       page_title not like '%/%'        and page_namespace = 10        and page_is_redirect = 0 

select page_title from page join revision on page_id = rev_page and page_title not like '%/%' and page_namespace = 10 and page_is_redirect = 0 

SELECT page_title FROM page WHERE page_namespace = 0     AND page_is_redirect = 0     AND page_title LIKE "%_کتاب_%" LIMIT 500; 

USE fawiki_p; SELECT page_title, rev_user_text, comment_text, rev_timestamp FROM revision JOIN page join comment  ON rev_page=page_id and rev_comment_id=comment_id WHERE page_namespace = 0 AND (   comment_text LIKE 'ویرایش%به_آخرین_تغییری_که%انجام_داده_بود%'   OR comment_text LIKE '%خرابکاری%خنثی‌سازی%وپ:توینکل%'   OR comment_text LIKE '%خرابکاری%خنثی‌سازی%وپ:تل%'   OR comment_text LIKE '%خنثی‌سازی%به_آخرین_نسخهٔ%وپ:توینکل%'   OR comment_text LIKE '%خنثی‌سازی%به_آخرین_نسخهٔ%وپ:تل%'   OR comment_text LIKE '%ویرایش%خرابکارانهٔ%به_آخرین_ویرایش%'   OR comment_text LIKE '%ویرایش%خرابکارانهٔ%بحث%واگردانی_شد%'   OR comment_text LIKE '%خرابکاری%به_نسخهٔ%واگردانده_شد.%' ) AND NOW() - INTERVAL 24 HOUR < rev_timestamp; 

USE fawiki_p; SELECT page_title, rev_user_text, comment_text, rev_timestamp FROM revision JOIN page join comment  ON rev_page=page_id and comment_text_id=comment_id WHERE page_namespace = 0 AND (   comment_text LIKE '%خنثی‌سازی_ویرایش%توسط%'   OR comment_text LIKE '%ویرایش%به_وسیلهٔ_[[ویکی‌پدیا:تفاوت_سریع|تفاوت_سریع]]_خنثی_شد.%'    OR comment_text LIKE '%آخرین_تغییر_متن_رد_شد%و_برگردانده_شد_به_نسخهٔ%'   OR comment_text LIKE '%تغییر_آخر_متن_رد_شد%و_برگردانده_شد_به_نسخهٔ%'   OR comment_text LIKE '%آخرین%تغییر_متن_رد_شد_و_برگردانده_شد_به_نسخهٔ%'      OR comment_text LIKE '%خنثی‌سازی%وپ:توینکل%'   OR comment_text LIKE '%خنثی‌سازی%وپ:تل%'   OR comment_text LIKE '%برگردانده%وپ:توینکل%'   OR comment_text LIKE '%برگردانده%وپ:تل%'   OR comment_text LIKE '%به_نسخهٔ%ویرایش%واگردانده_شد%وپ:توینکل%'   OR comment_text LIKE '%به_نسخهٔ%ویرایش%واگردانده_شد%وپ:تل%'   OR comment_text LIKE '%با_فرض_حسن_نیت%ویرایش%'   OR comment_text LIKE '%ویرایش‌های%با_فرض_حسن_نیت%خنثی‌سازی_شد%'   OR comment_text LIKE '%ویرایش%بحث%خنثی‌سازی_شد:%'   OR comment_text LIKE '%ویرایش%بحث%واگردانی_شد%'   OR comment_text LIKE '%ویرایش‌های%با_فرض_حسن_نیت%واگردانده_شد%'   OR comment_text LIKE '%واگردانی_به_نسخهٔ%وپ:توینکل%'   OR comment_text LIKE '%واگردانی%ویرایش%وپ:توینکل%'   OR comment_text LIKE '%تغییرات%به_نسخهٔ%واگردانده_شد%'   OR comment_text LIKE '%واگردانی_به_نسخهٔ%تاریخ%توسط%'     OR comment_text LIKE '%واگردانی_ویرایش‌های%وپ:هاگ%'   OR comment_text LIKE '%برگرداندن_ویرایش‌های%وپ:هاگ%' ) AND (   comment_text NOT LIKE '%خرابکاری%خنثی‌سازی%وپ:توینکل%'   AND comment_text NOT LIKE '%خرابکاری%خنثی‌سازی%وپ:تل%'   AND comment_text NOT LIKE '%خنثی‌سازی%به_آخرین_نسخهٔ%وپ:توینکل%'   AND comment_text NOT LIKE '%خنثی‌سازی%به_آخرین_نسخهٔ%وپ:تل%'   AND comment_text NOT LIKE '%ویرایش%خرابکارانهٔ%بحث_کاربر:%واگردانی_شد%' ) AND NOW() - INTERVAL 24 HOUR < rev_timestamp; 

USE fawiki_p; SELECT page_title, rev_user_text, comment_text, rev_timestamp FROM revision JOIN page join comment  ON rev_page=page_id and rev_comment_id=comment_id WHERE page_namespace = 0 AND (   comment_text LIKE '%خنثی‌سازی_ویرایش%توسط%'   OR comment_text LIKE '%ویرایش%به_وسیلهٔ_[[ویکی‌پدیا:تفاوت_سریع|تفاوت_سریع]]_خنثی_شد.%'    OR comment_text LIKE '%آخرین_تغییر_متن_رد_شد%و_برگردانده_شد_به_نسخهٔ%'   OR comment_text LIKE '%تغییر_آخر_متن_رد_شد%و_برگردانده_شد_به_نسخهٔ%'   OR comment_text LIKE '%آخرین%تغییر_متن_رد_شد_و_برگردانده_شد_به_نسخهٔ%'      OR comment_text LIKE '%خنثی‌سازی%وپ:توینکل%'   OR comment_text LIKE '%خنثی‌سازی%وپ:تل%'   OR comment_text LIKE '%برگردانده%وپ:توینکل%'   OR comment_text LIKE '%برگردانده%وپ:تل%'   OR comment_text LIKE '%به_نسخهٔ%ویرایش%واگردانده_شد%وپ:توینکل%'   OR comment_text LIKE '%به_نسخهٔ%ویرایش%واگردانده_شد%وپ:تل%'   OR comment_text LIKE '%با_فرض_حسن_نیت%ویرایش%'   OR comment_text LIKE '%ویرایش‌های%با_فرض_حسن_نیت%خنثی‌سازی_شد%'   OR comment_text LIKE '%ویرایش%بحث%خنثی‌سازی_شد:%'   OR comment_text LIKE '%ویرایش%بحث%واگردانی_شد%'   OR comment_text LIKE '%ویرایش‌های%با_فرض_حسن_نیت%واگردانده_شد%'   OR comment_text LIKE '%واگردانی_به_نسخهٔ%وپ:توینکل%'   OR comment_text LIKE '%واگردانی%ویرایش%وپ:توینکل%'   OR comment_text LIKE '%تغییرات%به_نسخهٔ%واگردانده_شد%'   OR comment_text LIKE '%واگردانی_به_نسخهٔ%تاریخ%توسط%'      OR comment_text LIKE '%واگردانی_ویرایش‌های%وپ:هاگ%'   OR comment_text LIKE '%برگرداندن_ویرایش‌های%وپ:هاگ%'       OR comment_text LIKE 'ویرایش%به_آخرین_تغییری_که%انجام_داده_بود%'   OR comment_text LIKE '%خرابکاری%خنثی‌سازی%وپ:توینکل%'   OR comment_text LIKE '%خرابکاری%خنثی‌سازی%وپ:تل%'   OR comment_text LIKE '%خنثی‌سازی%به_آخرین_نسخهٔ%وپ:توینکل%'   OR comment_text LIKE '%خنثی‌سازی%به_آخرین_نسخهٔ%وپ:تل%'   OR comment_text LIKE '%ویرایش%خرابکارانهٔ%به_آخرین_ویرایش%'   OR comment_text LIKE '%ویرایش%خرابکارانهٔ%بحث%واگردانی_شد%'   OR comment_text LIKE '%خرابکاری%به_نسخهٔ%واگردانده_شد.%' )  AND NOW() - INTERVAL 24 HOUR < rev_timestamp; 

USE fawiki_p; SELECT distinct log_title     FROM actor JOIN logging ON log_actor = actor_id LEFT JOIN user_groups ON log_actor = ug_user AND ug_group = 'bot'     WHERE log_type ='patrol'     AND log_action='patrol'     AND (       log_params LIKE '%auto";i:0;}'       OR log_params LIKE '%0'       )     AND log_title IN (       SELECT page_title       FROM page       WHERE page_namespace = 0     )     AND ug_group IS NULL     AND NOW() - INTERVAL 1 DAY <log_timestamp     GROUP BY log_title; --AND CURRENT_TIMESTAMP - log_timestamp < 00000001000000; /*AND log_timestamp > '20140101000000' AND log_timestamp < '20171028000000';*/ 

USE fawiki_p; SELECT  log_title, REGEXP_SUBSTR (REGEXP_SUBSTR (log_params,'s:8:"4::curid";s:8:"([0-9]+)"') ,'[0-9]{3,}' ) ,log_params,actor_name     FROM actor JOIN logging ON log_actor = actor_id Join  revision on log_page=rev_page LEFT JOIN user_groups ON log_actor = ug_user AND ug_group = 'bot'          WHERE log_type ='patrol'     AND log_action='patrol'     AND (       log_params LIKE '%auto";i:0;}'       OR log_params LIKE '%0'       )     AND log_title IN (       SELECT page_title       FROM page       WHERE page_namespace = 0     )     AND ug_group IS NULL     AND NOW() - INTERVAL 1 DAY <log_timestamp     GROUP BY log_title; 

USE fawiki_p; SELECT page_title, count(comment_text) FROM revision join page join comment  ON rev_page=page_id and comment_text_id=comment_id WHERE page_id=rev_page and page_namespace=0  and (comment_text LIKE '%واگردان%' or comment_text LIKE '%خنثی%') GROUP BY page_title  ORDER BY count(comment_text) DESC limit 20000; 

SELECT page_title FROM page WHERE page_namespace = 0     AND page_is_redirect = 0     AND (         (page_title LIKE "%1%")         OR (page_title LIKE "%2%")         OR (page_title LIKE "%3%")         OR (page_title LIKE "%4%")         OR (page_title LIKE "%5%")         OR (page_title LIKE "%6%")         OR (page_title LIKE "%7%")         OR (page_title LIKE "%8%")         OR (page_title LIKE "%9%")         OR (page_title LIKE "%0%")         ); 

SELECT page_title FROM page LEFT JOIN pagelinks ON pl_from = page_id WHERE page_namespace = 0     AND page_is_redirect = 0     AND pl_namespace IS NULL GROUP BY page_id; 

SELECT /*SLOW OK */ page_title,     page_namespace,     ll_title FROM page INNER JOIN langlinks ON page_id = ll_from WHERE page_namespace = 0     AND page_is_redirect = 0     AND ll_lang = 'en'     AND (         (ll_title LIKE "%Template:%")         OR (ll_title LIKE "%Category:%")         OR (ll_title LIKE "%Wikipedia:%")         OR (ll_title LIKE "%Portal:%")         OR (ll_title LIKE "%User:%")         OR (ll_title LIKE "%talk:%")         OR (ll_title LIKE "%Talk:%")         ) GROUP BY page_id; 

SELECT CONCAT (         ":",         page_title,         "]]||",         count(*),         "||",         page_len         ) FROM langlinks INNER JOIN page ON ll_from = page_id WHERE page_namespace = 0     AND page_is_redirect = 0     AND NOT EXISTS (         SELECT *         FROM langlinks AS t         WHERE t.ll_lang = "fa"             AND t.ll_from = langlinks.ll_from         ) GROUP BY ll_from ORDER BY count(*) DESC,     page_title limit 2000; 

SELECT /*SLOW OK*/ page_title FROM page WHERE page_id IN (         SELECT tl_from         FROM templatelinks         JOIN linktarget on tl_target_id = lt_id         WHERE lt_title = "Infobox_film"             AND lt_namespace = 10             AND tl_from IN (                 SELECT ll_from                 FROM langlinks                 WHERE ll_from                 GROUP BY ll_from                 HAVING COUNT(*) > 20                 )             AND tl_from NOT IN (                 SELECT ll_from                 FROM langlinks                 WHERE ll_lang = "fa"                 )         ); 

SELECT page_title FROM page WHERE page_namespace = 0     AND page_is_redirect = 0     AND page_title REGEXP '[A-Za-z]'     AND page_id NOT IN (         SELECT pp_page         FROM page_props         WHERE pp_propname = 'disambiguation'         ); 

SELECT page_title FROM page LEFT JOIN categorylinks ON cl_from = page_id WHERE page_namespace = 0     AND page_is_redirect = 0     AND ISNULL(cl_from); 

SELECT /*SLOW OK*/ page_title FROM page WHERE page_id IN (         SELECT tl_from         FROM templatelinks         WHERE tl_title = "جعبه_اطلاعات_روستای_ایران"             AND tl_namespace = 10             AND tl_from NOT IN (                 SELECT ll_from                 FROM langlinks                 WHERE ll_lang = "en"                 )         ); 

USE fawiki_p; SELECT DISTINCT page_title FROM page join externallinks WHERE page_namespace = 0 and el_from=page_id AND el_index LIKE'%en.wikipedia.org%' AND page_id NOT IN (                 SELECT ll_from                 FROM langlinks                 WHERE ll_lang = "en"                 ); 

SELECT /*SLOW OK*/ page_title FROM page WHERE page_id IN (         SELECT tl_from         FROM templatelinks         WHERE tl_title = "IranCensus2006"             AND tl_namespace = 10             AND tl_from NOT IN (                 SELECT ll_from                 FROM langlinks                 WHERE ll_lang = "fa"                 )         ); 

SELECT count(*) FROM revision join comment  ON rev_comment_id=comment_id WHERE comment_text LIKE '%به ویکی‌فا%'; 

USE fawiki_p; SELECT count(*) FROM revision join comment  ON  rev_comment_id=comment_id WHERE comment_text LIKE '%⟸%'; 

USE fawiki_p; select page_title from page where page_namespace = 0 and page_is_redirect = 0 and page_id in (select cl_from from categorylinks group by cl_from having count(*) < 3) limit 10; 

USE fawiki_p; SELECT DISTINCT pl_title,COUNT(pl_from) from pagelinks  WHERE pl_namespace = 0 and pl_from_namespace=0 	GROUP BY pl_title 	ORDER BY COUNT(pl_from) DESC limit 10; 

USE fawiki_p; SELECT /*SLOW OK*/ page_title FROM page WHERE page_namespace = 0 and page_is_redirect = 0 and page_id not IN (         SELECT tl_from         FROM templatelinks         WHERE tl_title = "Infobox" AND tl_namespace = 10)     AND page_id IN (                 SELECT ll_from                 FROM langlinks                 WHERE ll_from                 and ll_lang = "en"                 )     AND page_id NOT IN (         SELECT cl_from         FROM categorylinks         WHERE cl_to = "همه_صفحه‌های_ابهام‌زدایی"         )     AND page_id NOT IN (         SELECT cl_from         FROM categorylinks         WHERE cl_to = "مقالات_دارای_جعبه_اطلاعات"         ); 

USE fawiki_p; SELECT DISTINCT pl_title,COUNT(pl_from) FROM pagelinks JOIN page JOIN templatelinks WHERE page_id=tl_from AND (tl_title = "ابهام‌زدایی" OR tl_title = "ابهام‌زدایی_جغرافیا" OR tl_title = "ابهام‌زدایی_نام") AND pl_title=page_title AND pl_namespace = 0 AND pl_from_namespace=0 GROUP BY pl_title ORDER BY COUNT(pl_from) DESC; 

USE fawiki_p; SELECT DISTINCT page_title FROM revision join page join comment  ON rev_page=page_id and rev_comment_id=comment_id WHERE page_namespace=0  and page_id IN (SELECT tl_from  FROM templatelinks WHERE tl_title = "ابهام‌زدایی")  and comment_text LIKE '%منتقل کرد%'   and page_title in (SELECT DISTINCT pl_title from pagelinks WHERE pl_namespace = 0 and pl_from_namespace=0 GROUP BY pl_title ORDER BY COUNT(pl_from) DESC limit 1000) limit 100; 

select fap.page_title, fall.ll_title from fawiki_p.page as fap  right join fawiki_p.page_props as fapp on fap.page_id = fapp.pp_page  right join fawiki_p.langlinks as fall on fap.page_id = fall.ll_from where fap.page_namespace = 0  and fap.page_is_redirect = 0  and fapp.pp_propname = 'disambiguation' and fall.ll_lang = "en" and replace(fall.ll_title, ' ', '_') not in ( # enwiki disambig titles     select enp.page_title     from enwiki_p.page as enp right join enwiki_p.page_props as enpp on enp.page_id = enpp.pp_page     where enp.page_namespace = 0      and enp.page_is_redirect = 0     and enpp.pp_propname = 'disambiguation' ); 

select enp.page_title, enll.ll_title from enwiki_p.page as enp  right join enwiki_p.page_props as enpp on enp.page_id = enpp.pp_page  right join enwiki_p.langlinks as enll on enp.page_id = enll.ll_from where enp.page_namespace = 0  and enp.page_is_redirect = 0  and enpp.pp_propname = 'disambiguation' and enll.ll_lang = "fa" and replace(enll.ll_title, ' ', '_') not in ( # enwiki disambig titles     select fap.page_title     from fawiki_p.page as fap right join fawiki_p.page_props as fapp on fap.page_id = fapp.pp_page     where fap.page_namespace = 0      and fap.page_is_redirect = 0     and fapp.pp_propname = 'disambiguation' ); 

USE fawiki_p; SELECT /*SLOW OK*/ concat('*[[',page_title,']]') FROM page WHERE page_id IN (         SELECT tl_from         FROM templatelinks         WHERE tl_title = "بدون_منبع"             AND tl_namespace = 10) and page_id IN (         SELECT tl_from         FROM templatelinks         WHERE tl_title = "سرشناسی"             AND tl_namespace = 10)             AND page_id NOT IN (                 SELECT ll_from                 FROM langlinks                 WHERE ll_lang = "en"                 ); 

USE fawiki_p; SELECT    CONCAT ('# [[', S.page_title, ']] > [[:', ll_lang, ':', ll_title, ']]'),    T.page_title  FROM    page S     INNER JOIN       langlinks        ON S.page_id = ll_from     JOIN       redirect        ON rd_from = S.page_id     JOIN       page T        ON T.page_title = rd_title  WHERE    S.page_namespace = 0     AND S.page_is_redirect = 1     AND T.page_namespace = 0     AND T.page_is_redirect = 0 #     AND ll_lang = 'en'     AND NOT EXISTS     (       SELECT          null        FROM          langlinks        WHERE          ll_from = T.page_id     ) GROUP BY    S.page_title  ORDER BY    COUNT(ll_from) DESC; 

select p1.page_namespace, p1.page_title from page as p1 where mod(p1.page_namespace, 2) = 1 and p1.page_namespace <> 3 and p1.page_title not like "%/%" and p1.page_title not in (select p2.page_title from page as p2 where p2.page_title = p1.page_title and p2.page_namespace = p1.page_namespace - 1); 

SELECT /*SLOW OK */     DATE (         CONCAT (             YEAR(rev_timestamp),             "-",             MONTH(rev_timestamp),             "-",             DAY(rev_timestamp)             )         ) AS DAY,     COUNT(rev_timestamp) AS revisions FROM revision WHERE rev_user_text = 'Reza1615' GROUP BY DAY; 

USE fawiki_p; SELECT page_title,page_len, count(ll_lang) FROM revision INNER JOIN page ON rev_page = page_id join langlinks on page_id = ll_from where  WHERE not page_id in  (select cl_from from categorylinks where cl_to = 'همه_صفحه‌های_ابهام‌زدایی') and page_namespace = 0     AND rev_user_text = 'Reza1615'     AND rev_parent_id = 0     AND page_is_redirect = 0 GROUP BY page_title ORDER BY page_len; 

SELECT /*SLOW OK */ page_title,     DATE (         CONCAT (             YEAR(rev_timestamp),             "-",             MONTH(rev_timestamp),             "-",             DAY(rev_timestamp)             )         ) AS DAY FROM revision INNER JOIN page ON rev_page = page_id WHERE page_namespace = 0     AND rev_user_text = 'Reza1615'     AND rev_parent_id = 0     AND page.page_is_redirect = 0 GROUP BY DAY;  ORDER BY rev_timestamp; 

USE fawiki_p; SELECT DISTINCT page_title FROM revision join page join comment  ON rev_page=page_id and rev_comment_id=comment_id WHERE page_namespace=0  and comment_text LIKE 'Bot:foooooooo' and rev_timestamp > 20140912000000; 

SELECT DISTINCT /*SLOW OK */ page_title FROM categorylinks INNER JOIN page WHERE cl_to = page_title     AND page_is_redirect = 0; 

SELECT /* SLOW_OK */ cl_to FROM categorylinks WHERE cl_from IN (         SELECT DISTINCT ll_from         FROM langlinks         WHERE ll_lang = "fa"         )     AND cl_to NOT IN (         SELECT DISTINCT page_title         FROM langlinks         LEFT JOIN page ON page_id = ll_from         WHERE ll_lang = "fa"             AND page_namespace = 14         ) GROUP BY cl_to; 

USE fawiki_p;                SELECT /*SLOW OK*/ concat('*[[:Category:',page_title,']]')     from page join         category  on cat_title=page_title and page_namespace=14 and page_is_redirect = 0        LEFT JOIN templatelinks                ON tl_from = page_id AND tl_namespace = 10 and (cat_pages>0 or cat_subcats>0 or cat_files>0) AND tl_title not IN ( 'رده_خالی','رده_بهتر', 'رده_ابهام‌زدایی', 'رده_ردیابی‌کردن' ) where               page_id NOT IN (                 SELECT ll_from                 FROM langlinks                 WHERE ll_lang  in ("en","fr","it","ar","ckb","ru","nl")                 ) group by page_id; 

USE fawiki_p; SELECT page_title,         Count(ll_lang)  FROM   page         JOIN category           ON page_title = cat_title         LEFT JOIN categorylinks                ON page_title = cl_to         LEFT JOIN templatelinks                ON tl_from = page_id                   AND tl_title IN ( 'رده_خالی', 'رده_بهتر',                                     'رده_ابهام‌زدایی',                                     'رده_ردیابی‌کردن' )         LEFT JOIN langlinks                ON page_id = ll_from  WHERE  page_namespace = 14         AND page_is_redirect = 0         AND cl_to IS NULL         AND tl_title IS NULL  GROUP  BY page_title  ORDER  BY 2,            1; 

SELECT page_title FROM page INNER JOIN categorylinks WHERE cl_from = page_id     AND cl_to = 'مقاله‌های_انتخابی_نسخه_ویکی‌پروژه_نسخه_آفلاین' GROUP BY page_title; 

SELECT DISTINCT CONCAT ('*[[:رده:',page_title,']]') FROM  page WHERE page_id  IN (SELECT tl_from FROM templatelinks WHERE tl_title = 'رده_بهتر' AND tl_namespace = 10) AND page_id  IN (SELECT DISTINCT page_id                  FROM langlinks LEFT JOIN page                         ON page_id = ll_from                         WHERE ll_from IS NOT NULL  AND page_namespace = 14) AND page_namespace = 14; 

SELECT concat('#[[:رده:',page_title,']] > ',page_len) FROM page WHERE page_namespace = 14 and page_len>800 ORDER BY page_len DESC; 

FORMAT SQL Formatted SQL: SELECT    CONCAT ('[[',    fap.page_title,    ']]'),    CONCAT ('[[:en:',    enp.page_title,    ']]') ,    CONCAT ('[[File:',    limagelist.il_to,    '|72px]]> File:',    limagelist.il_to)       FROM    fawiki_p.page as fap  INNER JOIN    fawiki_p.langlinks as fal        ON   fap.page_id = fal.ll_from  inner join    enwiki_p.page as enp        on  fal.ll_title=enp.page_title  join    (       SELECT          *        FROM          enwiki_p.imagelinks as eni    ) as limagelist  WHERE    fap.page_namespace = 0      and fal.ll_lang = 'en'     AND fap.page_is_redirect = 0     and   fap.page_id not in (       SELECT          fai.il_from        FROM          fawiki_p.imagelinks as fai    )     and  enp.page_id=limagelist.il_from      and   not limagelist.il_to like 'Flag_of_%'     and   not limagelist.il_to like 'Ambox_%'     and   not limagelist.il_to like 'Wiktionary%'     and   not limagelist.il_to like 'Wikibooks%'     and   not limagelist.il_to like 'Wikivoyage%'     and   not limagelist.il_to like 'Incubator%'     and   not limagelist.il_to like 'Searchtool%'     and   not limagelist.il_to like 'Speech_balloon%'     and   not limagelist.il_to like 'Crystal_Clear%'     and   not limagelist.il_to like 'Speakerlink%'     and   not limagelist.il_to like 'Loudspeaker%'     and   not limagelist.il_to like 'Padlock%'     and   not limagelist.il_to like 'Nuvola_apps_%'     and   not limagelist.il_to like 'Wikiquote%'     and   not limagelist.il_to like 'Wikisource%'     and   not limagelist.il_to like 'Wikinews%'     and   not limagelist.il_to like 'Wikiversity%'     and   not limagelist.il_to like 'Question_book%'     and   not limagelist.il_to like 'Folder_Hexagonal%'     and   not limagelist.il_to like 'Portal-puzzle%'     and   not limagelist.il_to like 'Edit-clear%'     and   not limagelist.il_to like 'Text_document_with_red_question_mark%'     and   not limagelist.il_to like '%_stub%'     and   not limagelist.il_to like 'Rod_of_Asclepius%'     and   not limagelist.il_to like 'Merge-arrows%'     and   not limagelist.il_to like '%_icon%'     and   not limagelist.il_to like '%Balloon%'     and   not limagelist.il_to like 'Mergefrom%'     and   not limagelist.il_to like 'WikiProject%'     and   not limagelist.il_to like 'Yes_check%'     and   not limagelist.il_to like 'X_mark%'     and   not limagelist.il_to like 'Blank%'     and   not limagelist.il_to like '%_Icon%'     and   not limagelist.il_to like 'Symbol_book_class%'     and   not limagelist.il_to like 'Free_and_open-source_software_logo%'     and   not limagelist.il_to like 'Red_pog%'     and   not limagelist.il_to like 'Symbol_list_class%'     and   not limagelist.il_to like 'Allah-green%'     and   not limagelist.il_to like 'Symbol_support_vote%'     and   not limagelist.il_to like 'A_coloured_voting_box%'     and   not limagelist.il_to like 'Wiki_letter_w_cropped%'     and   not limagelist.il_to like 'Commons%'   limit 5000; 

USE fawiki_p; SELECT img_name,img_metadata FROM image WHERE  img_metadata not like '0' and img_user_text='foo'; 

SELECT /*SLOW OK */ CONCAT (         '* [[:commons:Image:',         db1.img_name,         ']] = [[:fa:Image:',         db2.img_name,         ']] (hash: ',         db1.img_sha1,         ')'         ) AS collision FROM commonswiki_p.image db1 INNER JOIN fawiki_p.image db2 ON db1.img_sha1 = db2.img_sha1 WHERE db1.img_sha1 <> ''; 

SELECT image_title,     page_title AS article_title FROM page,     (         SELECT il_to AS image_title,             MAX(il_from) AS article_id         FROM page,             imagelinks         WHERE page_title = il_to             AND page_id IN (                 SELECT page_id                 FROM page,                     templatelinks                 WHERE page_namespace = 6                     AND page_is_redirect = 0                     AND page_id = tl_from                     AND tl_title = 'محیط_نرم‌افزار'                     AND tl_namespace = 10                 )         GROUP BY il_to         ) AS imageuse WHERE page_id = article_id     AND page_namespace > 0; 

SELECT page_title,     tl_title FROM page INNER JOIN templatelinks WHERE page_id = tl_from     AND (         tl_title = 'سخ'         OR tl_title = 'سرخط'         OR tl_title = 'Imbox'         OR tl_title = 'File_other'         )     AND page_is_redirect = 0     AND tl_namespace = 10     AND page_namespace = 6 ORDER BY page_title; 

USE commonswiki_p;  SELECT gil_to FROM globalimagelinks WHERE gil_wiki = 'fawiki' limit 100; 

USE commonswiki_p;  SELECT CONCAT (         "# [[:File:",         gil_to,         "]] > [[:fa:",         gil_page_title,         "]]"         ) FROM globalimagelinks WHERE gil_wiki = 'fawiki'     AND gil_to IN (         SELECT tl_from         FROM templatelinks         WHERE tl_title = 'Uncategorized'         ) limit 100; 

SELECT pl_title,     page_title,     count(pl_title) FROM pagelinks INNER JOIN page ON pl_from = page_id INNER JOIN categorylinks ON page_id = cl_from WHERE pl_title NOT IN (         SELECT page_title         FROM page         WHERE page_namespace = 0         )     AND pl_namespace = 0     AND cl_to LIKE "%فیلم‌های_ایرانی_%"     AND page_namespace = 0 GROUP BY pl_title ORDER BY COUNT(pl_title) DESC; 

USE fawiki_p; SELECT COUNT(el_from) AS total, el_from, page_title FROM externallinks, page WHERE externallinks.el_from = page_id AND page_is_redirect = 0 AND page_namespace = 0 GROUP BY el_from ORDER BY total DESC; 

USE fawiki_p; SELECT COUNT(el_to) AS total, SUBSTRING_INDEX(el_to, '/', 3) AS search FROM externallinks, page WHERE page_id = el_from AND page_namespace = 0 GROUP BY search ORDER BY total DESC limit 500; 

USE fawiki_p; SELECT COUNT(  replace(replace(replace(replace(el_to, 'https:', ''), 'http:', ''),'//www.',''),'//','') ) AS total, replace(replace(replace(replace(SUBSTRING_INDEX(el_to, '/', 3), 'https:', ''), 'http:', ''),'//www.',''),'//','')  AS search FROM externallinks, page WHERE page_id = el_from AND page_namespace = 0 GROUP BY search ORDER BY total DESC limit 1000; 

USE fawiki_p; SELECT /*SLOW OK*/ page_title FROM page WHERE  page_namespace = 10 and page_is_redirect = 0 and page_id IN (         SELECT tl_from         FROM templatelinks         WHERE tl_title = "Asbox"             AND tl_namespace = 10         ) and page_id not IN (         SELECT tl_from         FROM templatelinks         WHERE tl_title = "الگوی_خرد"             AND tl_namespace = 10 ); 

SELECT page_title FROM page INNER JOIN langlinks ON page_id = ll_from WHERE page_namespace = 10     AND page_is_redirect = 0     AND (         page_title LIKE "%/doc%"         OR page_title LIKE "%/Doc%"         OR page_title LIKE "%/توضیحات%"         ) GROUP BY page_title ORDER BY COUNT(ll_from) DESC; 

USE fawiki_p;   SELECT page_title,         Count(pl_from)  FROM   page         LEFT JOIN pagelinks                ON page_id = pl_from  WHERE  pl_namespace = 0         AND pl_from_namespace = 10         AND page_namespace = 10         AND page_is_redirect = 0         AND pl_title IN (SELECT page_title                          FROM   page                          WHERE  page_namespace = 0                                 AND page_is_redirect = 1)  GROUP  BY page_title  ORDER  BY Count(pl_from) DESC LIMIT 1000; 

SELECT /*SLOW OK */ log_title,     log_comment,     log_action FROM logging WHERE log_type = 'delete'     AND log_action = 'delete'     AND log_namespace = 0 GROUP BY log_timestamp ORDER BY log_title; 

SELECT /*SLOW OK*/ page_title FROM page WHERE page_id IN (         SELECT tl_from         FROM templatelinks         WHERE tl_title = "Disambiguation"             AND tl_namespace = 10             AND tl_from IN (                 SELECT ll_from                 FROM langlinks                 WHERE ll_from                 GROUP BY ll_from                 HAVING COUNT(*) > 15                 )             AND tl_from NOT IN (                 SELECT ll_from                 FROM langlinks                 WHERE ll_lang = "fa"                 )         ); 

SELECT DISTINCT CONCAT (         '#[[user:',         page_title,         ']]'         ) FROM categorylinks INNER JOIN page WHERE cl_from = page_id     AND page_title LIKE "%/%"     AND page_namespace = 2     AND cl_to NOT IN (         SELECT page_title         FROM categorylinks         INNER JOIN page         WHERE cl_from = page_id             AND cl_to = "رده‌های_پنهان"             AND page_namespace = 14         ); 

USE wikidatawiki_p; SELECT CONCAT('* [[', ips_site_page, ']]') FROM wb_items_per_site JOIN wb_entity_per_page ON ips_item_id=epp_entity_id AND epp_entity_type='item' AND epp_redirect_target IS NULL LEFT JOIN pagelinks ON epp_page_id=pl_from AND pl_from_namespace=0 AND pl_namespace=0 WHERE ips_site_id='fawiki' AND pl_from IS NULL ORDER BY ips_site_page ASC; 

USE fawiki_p; SELECT page_namespace, page_title, pp_value AS item 	FROM (SELECT * FROM page_props WHERE pp_propname = 'wikibase_item') AS props 	JOIN (SELECT * FROM page WHERE page_is_redirect = 1) AS page 		ON page_id = pp_page ORDER BY page_title;