User:RobinHood70/Useful Queries

All queries on this page assume an appropriate USING command has already been issued, or a database has been selected in the GUI.

Detailed Job Queue

SELECT job_cmd, COUNT(*) AS Cnt
FROM job
GROUP BY job_cmd;

Find Parser Function Usage

Uses full-text search index (which ignores hash character) to narrow down the list, then re-uses the same table without a full-text search to detect the leading hash. Formats output as wikilinks for easy copy/paste. Does not detect full usage, only #pfunction. (Could be made to do so, but unless there are a lot of results, it's probably safest and easiest on our servers to let the user figure out what's really a parser function and what's not.)

SET @parfunc = 'icon';
SELECT CONCAT(':[[{{ns:', CAST(page_namespace AS char), '}}:', page_title, ']]') AS PageName
FROM page
WHERE page_id IN (
    SELECT si_page
    FROM searchindex
    WHERE MATCH (si_text) AGAINST (@parfunc)
        AND (si_text LIKE CONCAT('%#', @parfunc, '%'))
);

First-Person Search

Based on the query above, this query uses a whole-word regex to find only the words listed. It limits its search to gamespace only.

SELECT `page`.page_namespace, `page`.page_title, text.old_text
FROM `page`
        INNER JOIN
    revision ON `page`.page_latest = revision.rev_id
        INNER JOIN
    `text` ON revision.rev_text_id = `text`.old_id
WHERE
    `page`.page_namespace IN (100, 102, 104, 106, 108, 110, 112, 114, 116, 118, 120, 122, 124, 126, 128, 130, 132, 134, 136, 138, 140, 142, 144, 146, 148, 150, 200)
        AND old_text REGEXP '[[:<:]](i|i\'m|i\'ll|i\'ve|me|my|mine|myself)[[:>:]]'
ORDER BY `page`.page_namespace, `page`.page_title;

IPs Creating Lots of Accounts

Change HAVING clause to desired cutoff before running.

SELECT cuc_ip, COUNT(*) AS Aliases
FROM
    (SELECT DISTINCT cuc_user_text, cuc_ip
    FROM cu_changes
    WHERE cuc_actiontext = 'was created') AS derived1
GROUP BY cuc_ip
HAVING COUNT(*) >= 5
ORDER BY Aliases DESC

Somewhat clunky ranged version:

SELECT 
    CAST(LEFT(cuc_ip, LOCATE('.', cuc_ip, LOCATE('.', cuc_ip) + 1) - 1) AS CHAR) Address16,
    COUNT(*) Cnt
FROM cu_changes
WHERE cuc_actiontext = 'was created'
GROUP BY Address16
HAVING Cnt > 1
ORDER BY Cnt DESC

Pages With Incoming Links Only From User Subpages

SELECT 
    pagelinks.pl_namespace toNamespace,
    pagelinks.pl_title pToTitle
FROM
    uesp_net_wiki5.pagelinks
        INNER JOIN
    `page` pfrom ON pagelinks.pl_from = pfrom.page_id
        INNER JOIN
    `page` pto ON pagelinks.pl_namespace = pto.page_namespace
        AND pagelinks.pl_title = pto.page_title
WHERE
    pagelinks.pl_namespace != 2
        AND pagelinks.pl_namespace != 3
GROUP BY pagelinks.pl_namespace , pagelinks.pl_title
HAVING AVG(pfrom.page_namespace = 2
    AND pfrom.page_title LIKE '%/%') = 1
ORDER BY pagelinks.pl_namespace , pagelinks.pl_title

Pages Without Trails

A(n ever so slightly insane) query to pull up a list of pages that don't have trails. This could also have been done by bot, but would have required a lot of data retrieval. First stab, may need to be modified to exclude additional templates, depending what Silencer tells me. :)

The general idea is to list any page which doesn't use any of the templates from Category:Bread Crumb Trail Templates, or one of the indirect trail templates like {{Creature Summary}}. It only looks at custom namespaces, excluding their talk pages, as well as excluding subpages (for now...would need to remove that to check TR and Stirk properly).

SELECT 
    CONCAT(':[[{{NS:',
            CAST(page_namespace AS char),
            '}}:',
            page_title,
            ']]') AS PageName
FROM
    `page`
WHERE
    page_namespace IN (100, 102, 104, 106, 108, 110, 112, 114, 116, 118, 120, 122, 124, 126, 128, 130, 132, 134, 136, 138, 140, 142, 144, 146, 148, 150, 200)
        AND page_is_redirect = 0
        AND page_title NOT LIKE '%/%'
        AND page_id NOT IN (SELECT 
            templatelinks.tl_from
        FROM
            (SELECT 
                cl_from excludeTitle
            FROM
                categorylinks
            WHERE
                cl_to = 'Bread_Crumb_Trail_Templates' UNION SELECT 
                page_id
            FROM
                `page`
            WHERE
                page_namespace = 10
                    AND page_title IN ('Book_Summary' , 'City_Summary', 'Creature_Summary', 'Dagerfall_Services_Summary', 'Effect_Summary', 'Ingredient_Summary', 'Morrowind_Town_Table', 'Mod_Summary', 'NPC_Summary', 'Oblivion_World_Summary', 'Place_Summary', 'Shadowkey_NPC_Summary', 'Spell_Summary')) exclusions
                INNER JOIN
            `page` ON exclusions.excludeTitle = `page`.page_id
                INNER JOIN
            templatelinks ON `page`.page_title = templatelinks.tl_title)
ORDER BY page_namespace , page_title
LIMIT 0 , 1000

Pages Without Visible Categories

This query will display all pages that have no visible categories. Note that red-linked categories are treated the same as hidden categories. It's moderately long-running, at about 30 seconds.

SELECT 
    CONCAT(':[[{{NS:',
            CAST(page_namespace AS char),
            '}}:',
            page_title,
            ']]') AS PageName
FROM
    `page`
        LEFT JOIN
    (SELECT DISTINCT
        categorylinks.cl_from
    FROM
        (categorylinks
    INNER JOIN `page` ON categorylinks.cl_to = `page`.page_title)
    LEFT JOIN (SELECT 
        `page`.page_id
    FROM
        page_props
    INNER JOIN `page` ON `page`.page_id = page_props.pp_page
    WHERE
        page_props.pp_propname = 'hiddencat'
            AND `page`.page_namespace = 14) hiddenCats ON `page`.page_id = hiddenCats.page_id
    WHERE
        hiddenCats.page_id IS NULL) visCats ON `page`.page_id = visCats.cl_from
WHERE
    `page`.page_namespace IN (100, 102, 104, 106, 108, 110, 112, 114, 116, 118, 120, 122, 124, 126, 128, 130, 132, 134, 136, 138, 140, 142, 144, 146, 148, 150, 200)
        AND `page`.page_is_redirect = 0
        AND visCats.cl_from IS NULL

Skin User Count

This counts the users of custom skins for all time. The default skin (currently uespmonobook) is not recorded, so will not show up in this simple query.

SELECT 
    CAST(up_value AS CHAR) skin, COUNT(*) count
FROM
    uesp_net_wiki5.user_properties
WHERE
    up_property = 'skin'
GROUP BY CAST(up_value AS CHAR)
ORDER BY COUNT(*) DESC

This is a more advanced version, which shows only recent users (2017 forwards) who have made at least one edit, and does show the default skin.

SELECT 
    COALESCE(skin, '(default)') cskin, COUNT(*) count
FROM
    uesp_net_wiki5.user
        LEFT JOIN
    (SELECT 
        up_user, CAST(user_properties.up_value AS CHAR) skin
    FROM
        user_properties
    WHERE
        up_property = 'skin') skins ON user.user_id = skins.up_user
WHERE
    user_touched > 20170000000000 AND user_editcount > 0
GROUP BY skin
ORDER BY COUNT(*) DESC

Slow Search

This search is database intensive and should be avoided except in cases where the MySQL/Lucene full text searches can't do the job. To minimize impact, searching has been limited to article space only, excluding User space. Because namespaces are only translated to names via PHP, only the numeric namespace is available. Use PHP or an API query to get the correct namespace text.

SELECT `page`.page_namespace, `page`.page_title, text.old_text
FROM `page`
        INNER JOIN
    revision ON `page`.page_latest = revision.rev_id
        INNER JOIN
    `text` ON revision.rev_text_id = `text`.old_id
WHERE
    `page`.page_namespace IN (0, 4, 6, 8, 10, 12, 14, 100, 102, 104, 106, 108, 110, 112, 114, 116, 118, 120, 122, 124, 126, 128, 130, 132, 134, 136, 138, 140, 142, 144, 146, 148, 150, 200)
        AND old_text LIKE '% the the %'
ORDER BY `page`.page_namespace, `page`.page_title;

Uncategorized Redirects

Lists all redirects that don't have any category links on the page. Note that the namespaces {{ns:6}} (File) and {{ns:14}} (Category) should be manually replaced to have leading colons before saving the results to the wiki. While arguable, I think this is much cleaner to do in a text editor than in SQL.

SELECT CONCAT('* [[{{ns:', page.page_namespace, '}}:', REPLACE(page.page_title, '_', ' '), ']]') as Link
FROM uesp_net_wiki5.page
        LEFT JOIN
    categorylinks ON page.page_id = categorylinks.cl_from
WHERE page_is_redirect = 1
    AND categorylinks.cl_from IS NULL
ORDER BY page_namespace , page_title;

Undocumented Templates

Shows templates, excluding redirects, with no corresponding /Doc page.

SELECT templates.page_title
FROM uesp_net_wiki5.page templates
        LEFT JOIN
    page docs ON (templates.page_namespace = docs.page_namespace) AND (docs.page_title = CONCAT(templates.page_title, '/Doc'))
WHERE templates.page_namespace = 10
    AND templates.page_is_redirect = 0
    AND templates.page_title NOT LIKE '%/%'
    AND docs.page_namespace IS NULL
ORDER BY page_title

Watchlist Top 20

As implied by the field name, the total number of pages is double what's normally shown on the wiki, since the table includes both article and talk pages separately.

SELECT 
    user_name, COUNT(*) AS DoubledTotal
FROM
    watchlist
        INNER JOIN
    user ON watchlist.wl_user = user.user_id
GROUP BY watchlist.wl_user
ORDER BY COUNT(*) DESC
LIMIT 20