Help:MySQL queries

MySQL queries allow you to pull data from the Tool Labs replicated databases. (The original page has been imported from Toolserver MySQL queries)

Database layout

The database layout is available at the MediaWiki wiki: mw:Manual:Database layout.

A dump of the currently running database layouts can be found here.

There are also two commands you can use to view the layout.   SHOW TABLES   will show the available tables in a database.   DESCRIBE table_name   will show the available columns in a specific table.

Slices

The various databases are stored in slices. The slices are named with a preceding 's' and a digit, e.g., s1, s2, etc. followed by the internal domain name 'labsdb' (e.g. s1.labsdb). A table of this information is available here: List meta_p.wiki.

Data storage

There are a few tricks to how data is stored in the various tables.

1. Page titles use underscores and never include the namespace prefix. (eg: page_title='The_Lord_of_the_Rings')
2. User names use spaces, not underscores. (eg: rev_user_text='Jimbo Wales'
3. Namespaces are integers. A key to the integers is available here.

Views

Tool Labs has exact replicas of Wikimedia's databases, however, certain information is restricted to Tool Labs users using MySQL views.

For example, the user table view does not show things like user_password or user_email to Tool Labs users. You can only access data from the public (redacted) databases marked as _p (eg: enwiki_p).

Alternative Views

There are some alternative views in which the data from the underlying tables are redacted in a different way, so that the corresponding indices can be used. If you utilize the listed columns in your queries (especially in WHERE clause or ORDER BY statement) it is recommended to use these alternative views. This will speed up things quite a lot.

columns	canonical view	alternative view (recommended)
ar_user	archive	archive_userindex
ar_user_text
rev_user	revision	revision_userindex
rev_user_text
log_namespace	logging	logging_logindex
log_title
log_page
log_user	logging	logging_userindex
log_user_text
oi_user	oldimage	oldimage_userindex
oi_user_text
rc_user	recentchanges	recentchanges_userindex
rc_user_text
rev_user	revision	revision_userindex
rev_user_text

Federated Tables

To enable access data across different slices (eg: enwiki and commonswiki) from within MySQL, there are so called federated tables which are stored in schemas marked as '_f_p'. An example is shown in #File_description_pages_without_an_associated_file

Right now there exist federated tables for the following wikis:

• commonswiki_f_p (on every slice)
• wikidatawiki_f_p (on every slice)

Federated tables are set up by MySQL admin's. If you need an additional one, you may request it in bugzilla. Further information can be found at The Federated Storage Engine

Wiki text

Unfortunately there is no way to access the wikitext directly via MySQL replica databases. You have to use the the mw:API instead.

Accessing the databases

There are a variety of ways to access the databases.

preset shell script

From the command line, a shell script exists that automatically selects the correct slice for you. The sql shell script is a wrapper around the mysql command. Either method works, though the sql shell script selects the appropriate slice for you.

$ sql enwiki_p

This command will connect to the appropriate cluster (in this case, s1.labsdb) and give you a MySQL prompt where you can run queries.

$ sql enwiki_p < test-query.sql > test-query.txt

This command takes a .sql file that contains your query, selects the appropriate slice, runs the query, and outputs to a text file. The advantage here is that it doesn't add lines around the data (making a table), it instead outputs the data in a tab-delimited format.

$ sql enwiki_p < test-query.sql | gzip >test-query.txt.gz

This command does the same thing as the command above, but after outputting to a text file, it gzips the data. This can be very helpful if you're dealing with large amounts of data.

mysql command

If you wish to use the mysql command directly, a sample query would look like this:

$ mysql --defaults-file=~/replica.my.cnf -h s1.labsdb enwiki_p -e "SHOW databases;"

The --defaults-file option points to the file replica.my.cnf where your MySQL credentials are stored (username, password). The -h option tells MySQL which host to access (in this case s1.labsdb) and enwiki_p is the database you want to access. The -e option tells MySQL to execute the following command enclosed in quotes. You can also have MySQL output the results to a file.

$ mysql --defaults-file=~/replica.my.cnf -h s1.labsdb enwiki_p < test-query.sql > test-query.txt

mysql command with default

Another way to use the mysql command directly but without the need to specify the --defaults-file option each time, is to copy your replica.my.cnf to .my.cnf once.

$ cp replica.my.cnf .my.cnf

From that point your login data is automatically invoked with mysql command. All subsequent commands then look similar as shown above.

$ mysql -h s1.labsdb -e "show databases;"

Writing queries

Because the replica databases are read-only, nearly all of the queries you will want to run will be SELECT queries.

SELECT * FROM `user`;

This query selects all columns from the user table. More information about MySQL queries are available below (in the example queries) and in the MySQL SELECT.

Queries end in a semi-colon (;). If you want to cancel the query, end it in \c. If you want to output in a non-table format, use \G.

meta_p database

The "meta_p" database contains metadata about the various wikis and databases. It consists of one table: wiki and it is available on every slice (s1–s6.labsdb).

List meta_p.wiki

Example queries

en:Wikipedia:Database reports (and its equivalents in other languages) contain many useful examples.

tsreports is another source of valuable suggestions

Done (Tool Labs) = These scripts have been tested (and if necessary modified) for Tool Labs

Atypical log entries

SELECT
  user_name,
  log_namespace,
  log_timestamp,
  log_action,
  log_title,
  log_comment
FROM logging
JOIN `user`
ON log_user = user_id
WHERE log_type='delete'
AND log_action != 'restore'
AND log_action != 'delete';

Explanation: This pulls log entries from the deletion log that aren't restore or delete actions. Done (Tool Labs)

Broken redirects

See also source code of Special:BrokenRedirects

SELECT
  p1.page_namespace,
  p1.page_title
FROM redirect AS rd
JOIN page p1
ON rd.rd_from = p1.page_id
LEFT JOIN page AS p2
ON rd_namespace = p2.page_namespace
AND rd_title = p2.page_title
WHERE rd_namespace >= 0
AND p2.page_namespace IS NULL
ORDER BY p1.page_namespace ASC;

Explanation: This pulls all broken redirects. Done/modified (Tool Labs)

Cross-namespace redirects

SELECT
  pt.page_namespace,
  pf.page_title,
  rd_title
FROM redirect, page AS pf, page AS pt
WHERE pf.page_namespace = 0
AND rd_title = pt.page_title
AND rd_namespace = pt.page_namespace
AND pt.page_namespace != 0
AND rd_from = pf.page_id
AND pf.page_namespace = 0;

Explanation: This pulls redirects from (Main) to any other namespace. Done /modified (Tool Labs)

Deleted red-linked categories

SELECT
  cattmp.cl_to,
  cattmp.cl_count,
  user_name,
  log_timestamp,
  log_comment
FROM logging
JOIN `user` ON log_user = user_id
JOIN
(SELECT
   cl_to,
   COUNT(cl_to) AS cl_count
 FROM categorylinks
 LEFT JOIN page ON cl_to = page_title
 AND page_namespace = 14
 WHERE page_title IS NULL
 GROUP BY cl_to) AS cattmp
ON log_title = cattmp.cl_to
WHERE log_namespace = 14
AND log_type = "delete"
AND log_timestamp = (SELECT
                       MAX(log_timestamp)
                     FROM logging AS last
                     WHERE log_namespace = 14
                     AND cattmp.cl_to = last.log_title);

Explanation: This pulls non-existent used categories that have previously been deleted. Done (Tool Labs)

Empty categories

SELECT
  page_title,
  page_len
FROM categorylinks
RIGHT JOIN page ON cl_to = page_title
WHERE page_namespace = 14
AND page_is_redirect = 0
AND cl_to IS NULL
AND NOT EXISTS (SELECT
                  1
                FROM categorylinks
                WHERE cl_from = page_id
                AND cl_to = 'Wikipedia_category_redirects')
AND NOT EXISTS (SELECT
                  1
                FROM categorylinks
                WHERE cl_from = page_id
                AND cl_to = 'Disambiguation_categories')
AND NOT EXISTS (SELECT
                  1
                FROM templatelinks
                WHERE tl_from = page_id
                AND tl_namespace = 10
                AND tl_title = 'Empty_category');

Explanation: This pulls empty categories that aren't in specific categories and don't transclude a specific template. Done Tools Labs

Fully-protected articles with excessively long expiries

SELECT
  page_is_redirect,
  page_title,
  user_name,
  logs.log_timestamp,
  pr_expiry,
  logs.log_comment
FROM page
JOIN page_restrictions ON page_id = pr_page
AND page_namespace = 0
AND pr_type = 'edit'
AND pr_level = 'sysop'
AND pr_expiry > DATE_FORMAT(DATE_ADD(NOW(),INTERVAL 1 MONTH),'%Y%m%d%H%i%s')
AND pr_expiry != 'infinity'
LEFT JOIN logging AS logs ON logs.log_title = page_title
                         AND logs.log_namespace = 0
                         AND logs.log_type = 'protect'
LEFT JOIN `user` ON logs.log_user = user_id 
WHERE CASE WHEN (NOT ISNULL(log_timestamp)) 
  THEN log_timestamp = (SELECT MAX(last.log_timestamp)
                        FROM logging AS last 
                        WHERE log_title = page_title 
                        AND log_namespace = 0
                        AND log_type = 'protect') 
  ELSE 1 END;

Explanation: Articles that are fully-protected from editing for more than one month. Done /modified (Tool Labs)

Excessively long IP blocks

SELECT
  ipb_address,
  ipb_by_text,
  ipb_timestamp,
  ipb_expiry,
  ipb_reason
FROM ipblocks
WHERE ipb_expiry > DATE_FORMAT(DATE_ADD(NOW(),INTERVAL 2 YEAR),'%Y%m%d%H%i%s')
AND ipb_expiry != "infinity"
AND ipb_user = 0;

Explanation: Blocks of anonymous users that are longer than two years (but not indefinite). Done (Tool Labs)

Indefinitely fully-protected articles

SELECT
  page_is_redirect,
  page_title,
  user_name,
  logs.log_timestamp,
  logs.log_comment
FROM page
JOIN page_restrictions ON page_id = pr_page
AND page_namespace = 0
AND pr_type = 'edit'
AND pr_level = 'sysop'
AND pr_expiry = 'infinity'
LEFT JOIN logging AS logs ON logs.log_title = page_title
                         AND logs.log_namespace = 0
                         AND logs.log_type = 'protect'
LEFT JOIN `user` ON logs.log_user = user_id 
WHERE CASE WHEN (NOT ISNULL(log_timestamp)) 
  THEN log_timestamp = (SELECT MAX(last.log_timestamp)
                        FROM logging AS last 
                        WHERE log_title = page_title 
                        AND log_namespace = 0
                        AND log_type = 'protect') 
  ELSE 1 END;

Explanation: Articles indefinitely fully-protected from editing. Done (Tool Labs)

Long pages

SELECT
  page_namespace,
  page_title,
  page_len
FROM page
WHERE page_len > 175000
AND page_title NOT LIKE "%/%"
ORDER BY page_namespace ASC;

Explanation: Pages over 175,000 bytes in length; excludes titles with "/" in them (to avoid archives, etc.). Done /modified (Tool Labs)

Redirects obscuring page content

SELECT
  page_namespace,
  page_title,
  page_len
FROM page
WHERE page_is_redirect = 1
HAVING page_len > 449
ORDER BY page_namespace ASC;

Explanation: Redirects with large page lengths. This usually indicates there is text below the redirect that should not be there. Done /modified (Tool Labs)

Mistagged non-free content

SELECT
  DISTINCT(enwiki_p.page.page_title),
  commonswiki_p.image.img_name
FROM enwiki_p.image, commonswiki_p.image, enwiki_p.categorylinks, enwiki_p.page
WHERE enwiki_p.image.img_sha1 = commonswiki_p.image.img_sha1
AND enwiki_p.page.page_title = enwiki_p.image.img_name
AND enwiki_p.categorylinks.cl_from = enwiki_p.page.page_id
AND enwiki_p.categorylinks.cl_to = 'All_non-free_media'
AND enwiki_p.image.img_sha1 != 'phoiac9h4m842xq45sp7s6u21eteeq1';

Explanation: This pulls files on a local wiki that are in non-free category, but also exist at Commons. This indicates that either the local image or the Commons image should be deleted. It excludes the SHA1 empty string due to bad database rows.

Pages with the most revisions

SELECT
  page_namespace,
  ns_name,
  page_title,
  COUNT(*)
FROM revision
JOIN page ON page_id = rev_page
JOIN toolserver.namespace ON page_namespace = ns_id
AND dbname = 'enwiki_p'
GROUP BY page_namespace, page_title
ORDER BY COUNT(*) DESC
LIMIT 1000;

Explanation: This pulls the pages with the most revisions. On large wikis, it can take several hours (or days) to run.

Page counts by namespace

SELECT
  page_namespace,
  MAX(notredir),
  MAX(redir)
FROM (
  SELECT page.page_namespace,
         IF( page_is_redirect, COUNT(page.page_namespace), 0 ) AS redir,
         IF( page_is_redirect, 0, COUNT(page.page_namespace)) AS notredir
  FROM page
  GROUP BY page_is_redirect, page_namespace
  ORDER BY page_namespace, page_is_redirect
) AS pagetmp
GROUP BY page_namespace;

Explanation: This pulls the number of redirects and non-redirects in each namespace. Done /modified (Tool Labs)

Orphaned talk pages

SELECT
  p1.page_namespace,
  p1.page_title
FROM page AS p1
WHERE p1.page_title NOT LIKE "%/%"
AND p1.page_namespace NOT IN (0,2,3,4,6,8,9,10,12,14,16,18,100,102,104,108, 118, 710, 828, 2300)
AND CASE WHEN p1.page_namespace = 1
  THEN NOT EXISTS (SELECT
                     1
                   FROM page AS p2
                   WHERE p2.page_namespace = 0
                   AND p1.page_title = p2.page_title)
  ELSE 1 END
AND CASE WHEN p1.page_namespace = 5
  THEN NOT EXISTS (SELECT
                     1
                   FROM page AS p2
                   WHERE p2.page_namespace = 4
                   AND p1.page_title = p2.page_title)
  ELSE 1 END
AND CASE WHEN p1.page_namespace = 7
  THEN NOT EXISTS (SELECT
                     1
                   FROM page AS p2
                   WHERE p2.page_namespace = 6
                   AND p1.page_title = p2.page_title)
  AND NOT EXISTS (SELECT
                    1
                  FROM commonswiki_p.page AS p2
                  WHERE p2.page_namespace = 6
                  AND p1.page_title = p2.page_title)
  ELSE 1 END
AND CASE WHEN p1.page_namespace = 11
  THEN NOT EXISTS (SELECT
                     1
                   FROM page AS p2
                   WHERE p2.page_namespace = 10
                   AND p1.page_title = p2.page_title)
  ELSE 1 END
AND CASE WHEN p1.page_namespace = 13
  THEN NOT EXISTS (SELECT
                     1
                   FROM page AS p2
                   WHERE p2.page_namespace = 12
                   AND p1.page_title = p2.page_title)
  ELSE 1 END
AND CASE WHEN p1.page_namespace = 15
  THEN NOT EXISTS (SELECT
                     1
                   FROM page AS p2
                   WHERE p2.page_namespace = 14
                   AND p1.page_title = p2.page_title)
  ELSE 1 END
AND CASE WHEN p1.page_namespace = 17
  THEN NOT EXISTS (SELECT
                     1
                   FROM page AS p2
                   WHERE p2.page_namespace = 16
                   AND p1.page_title = p2.page_title)
  ELSE 1 END
AND CASE WHEN p1.page_namespace = 101
  THEN NOT EXISTS (SELECT
                     1
                   FROM page AS p2
                   WHERE p2.page_namespace = 100
                   AND p1.page_title = p2.page_title)
  ELSE 1 END
AND CASE WHEN p1.page_namespace = 109
  THEN NOT EXISTS (SELECT
                     1
                   FROM page AS p2
                   WHERE p2.page_namespace = 108
                   AND p1.page_title = p2.page_title)
  ELSE 1 END
AND CASE WHEN p1.page_namespace = 119
  THEN NOT EXISTS (SELECT
                     1
                   FROM page AS p2
                   WHERE p2.page_namespace = 118
                   AND p1.page_title = p2.page_title)
  ELSE 1 END
AND CASE WHEN p1.page_namespace = 711
  THEN NOT EXISTS (SELECT
                     1
                   FROM page AS p2
                   WHERE p2.page_namespace = 710
                   AND p1.page_title = p2.page_title)
  ELSE 1 END
AND CASE WHEN p1.page_namespace = 829
  THEN NOT EXISTS (SELECT
                     1
                   FROM page AS p2
                   WHERE p2.page_namespace = 828
                   AND p1.page_title = p2.page_title)
  ELSE 1 END
AND CASE WHEN p1.page_namespace = 2301
  THEN NOT EXISTS (SELECT
                     1
                   FROM page AS p2
                   WHERE p2.page_namespace = 2300
                   AND p1.page_title = p2.page_title)
  ELSE 1 END
AND p1.page_id NOT IN (SELECT
                         page_id
                       FROM page
                       JOIN templatelinks
                       ON page_id = tl_from
                       WHERE tl_title="G8-exempt"
                       AND tl_namespace = 10)
AND p1.page_id NOT IN (SELECT
                         page_id
                       FROM page
                       JOIN templatelinks
                       ON page_id = tl_from
                       WHERE tl_title="Rtd"
                       AND tl_namespace = 10);

Explanation: This (very, very hackishly) pulls all pages in the talk namespaces that don't have a corresponding subject-space page. It JOINs against Commons to ensure that File_talk: pages are truly orphaned. It also has some en.wiki-specific template checks in it. Done /modified (Tool Labs)

This still reports flow pages in NS 2600.

Ownerless pages in the user space

SELECT
  page_namespace,
  page_title,
  page_len
FROM page
LEFT JOIN `user`
ON user_name = REPLACE(page_title, '_', ' ')
WHERE page_namespace IN (2,3)
AND page_is_redirect = 0
AND page_title NOT LIKE "%/%"
AND page_title NOT RLIKE "(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)"
AND user_name IS NULL;

Explanation: This pulls all User: and User_talk: pages not belonging to a registered user. Pages belonging to an anonymous user are excluded. Done /modified (Tool Labs)

This doesn't exclude IPv6 addresses.

Polluted categories

SELECT DISTINCT
  cl_to
FROM categorylinks AS cat
JOIN page AS pg1
ON cat.cl_from = pg1.page_id
WHERE page_namespace = 2
AND EXISTS (SELECT
              1
            FROM page AS pg2
            JOIN categorylinks AS cl
            ON pg2.page_id = cl.cl_from
            WHERE pg2.page_namespace = 0
            AND cat.cl_to = cl.cl_to)
AND cl_to NOT IN (SELECT
                    page_title
                  FROM page
                  JOIN templatelinks
                  ON tl_from = page_id
                  WHERE page_namespace = 14
                  AND tl_namespace = 10
                  AND tl_title = 'Pollutedcat')
LIMIT 250;

Explanation: This pulls categories that contain pages in the (Main) namespace and the User: namespace. Generally categories hold one or the other. Done (Tool Labs)

Categories categorized in red-linked categories

SELECT
  page_title,
  cl_to
FROM page
JOIN
(SELECT
   cl_to,
   cl_from
 FROM categorylinks
 LEFT JOIN page ON cl_to = page_title
 AND page_namespace = 14
 WHERE page_title IS NULL) AS cattmp
ON cattmp.cl_from = page_id
WHERE page_namespace = 14;

Explanation: This pulls categories categorized in red-linked categories. Done (Tool Labs)

Articles containing red-linked files

SELECT
  page_title,
  il_to
FROM page
JOIN imagelinks
ON page_id = il_from
WHERE (NOT EXISTS(
 SELECT
   1
 FROM image
 WHERE img_name = il_to))
AND (NOT EXISTS(
 SELECT
   1
 FROM commonswiki_f_p.page
 WHERE page_title = il_to
 AND page_namespace = 6))
AND page_namespace = 0;

Explanation: This pulls articles that contain red-linked images. It checks both Commons and the local wiki. Done /modified (Tool Labs)

Self-categorized categories

SELECT
  page_title,
  cat_pages,
  cat_subcats
FROM page
JOIN categorylinks ON cl_to = page_title
RIGHT JOIN category
ON cat_title = page_title
WHERE page_id = cl_from
AND page_namespace = 14;

Explanation: This pulls self-categorized categories. Done (Tool Labs)

Uncategorized categories

SELECT
  page_title,
  page_len,
  cat_pages,
  rev_timestamp,
  rev_user_text
FROM revision
JOIN
(SELECT
   page_id,
   page_title,
   page_len,
   cat_pages
 FROM category
 RIGHT JOIN page ON cat_title = page_title
 LEFT JOIN categorylinks ON page_id = cl_from
 WHERE cl_from IS NULL
 AND page_namespace = 14
 AND page_is_redirect = 0) AS pagetmp
ON rev_page = pagetmp.page_id
AND rev_timestamp = (SELECT MAX(rev_timestamp)
                     FROM revision AS last 
                     WHERE last.rev_page = pagetmp.page_id);

Explanation: This pulls uncategorized categories.

Pages in a specific category using a specific template

SELECT
  page_title
FROM page
JOIN templatelinks
ON tl_from = page_id
JOIN categorylinks
ON cl_from = page_id
WHERE cl_to = "Living_people"
AND tl_namespace = 10
AND tl_title = "Fact"
AND page_namespace = 0
LIMIT 500;

Explanation: This pulls pages that are in a specific category and are using a specific template.

Top edit timestamp for a category of users

SELECT
  rev_user_text,
  rev_timestamp
FROM revision
JOIN (SELECT
        page_title
      FROM page
      JOIN categorylinks
      ON cl_from = page_id
      WHERE cl_to = 'Category_name_goes_here'
      AND page_namespace = 2
      AND page_title NOT LIKE '%/%') AS cltmp
ON REPLACE(cltmp.page_title, '_', ' ') = rev_user_text
WHERE rev_timestamp = (SELECT
                         MAX(rev_timestamp)
                       FROM revision
                       WHERE rev_user_text = REPLACE(cltmp.page_title, '_', ' '));

Explanation: This will take the user pages that do not contain a forward slash ("/") in "Category_name_goes_here" and get the top edit timestamp for each user.

Top pages by in a specific namespace for a specific user

SELECT
  `page_title`,
  COUNT(*)
FROM `revision`
JOIN `page`
ON `page_id` = `rev_page`
JOIN `user` 
ON `user_id` = `rev_user`
WHERE `user_name` = 'User name'
AND `page_namespace` = 4
GROUP BY `page_title`
ORDER BY COUNT(*) DESC
LIMIT 25;

Explanation: This will pull the page titles of a the most-edited pages by a specific user in a specific namespace.

Number of deletions per day for a specific user

SELECT
  DATE( CONCAT( YEAR( log_timestamp), "-", MONTH( log_timestamp ), "-", DAY( log_timestamp ) ) ) AS day,
  COUNT(log_timestamp) AS deletions
FROM logging
JOIN `user`
ON log_user = user_id
WHERE user_name = 'User name'
AND log_type = 'delete'
AND log_action = 'delete'
GROUP BY day;

Explanation: This will group the number of deletions per day by a specific user.

Number of deletions per day

SELECT
  DATE(CONCAT(YEAR(log_timestamp),"-",MONTH(log_timestamp),"-",DAY(log_timestamp))) AS day,
  COUNT(log_id) AS deletions
FROM logging
WHERE log_type = 'delete'
AND log_action = 'delete'
GROUP BY day;

Explanation: This will pull the number of deletions per day on a given wiki. Done (Tool Labs)

Most common deletion summaries

SELECT
  log_comment,
  COUNT(log_id)
FROM logging
WHERE log_type = 'delete'
AND log_action = 'delete'
GROUP BY log_comment
ORDER BY COUNT(log_id) DESC;

Explanation: This will pull the most common deletion summaries on a given wiki.

Most common deletion summaries for a specific user

SELECT
  log_comment AS reason,
  COUNT(*) AS uses
FROM logging_userindex
JOIN `user`
ON log_user = user_id
WHERE user_name = 'User name'
AND log_type = 'delete'
AND log_action = 'delete'
GROUP BY reason
ORDER BY uses DESC
LIMIT 25;

Explanation: This will pull the most commonly used deletion summaries for a specific user. Done /modified (Tool Labs)

Most common edit summaries for a specific user

SELECT
  rev_comment,
  COUNT(*)
FROM revision_userindex
WHERE rev_user_text = 'User name'
GROUP BY rev_comment
ORDER BY COUNT(*) DESC
LIMIT 25;

Explanation: This will pull the most commonly used edit summaries for a specific user. Done /modified (Tool Labs)

Number of revisions per day

SELECT
  DATE(CONCAT(YEAR(rev_timestamp),"-",MONTH(rev_timestamp),"-",DAY(rev_timestamp))) AS day,
  COUNT(rev_timestamp) AS revisions
FROM revision
GROUP BY day;

Explanation: This will pull the number of (non-deleted) revisions to a particular wiki and group the numbers by day. Done (Tool Labs)

Number of revisions per day by a specific user

SELECT
  DATE(CONCAT(YEAR(rev_timestamp),"-",MONTH(rev_timestamp),"-",DAY(rev_timestamp))) AS day,
  COUNT(rev_timestamp) AS revisions
FROM revision_userindex
WHERE rev_user_text = 'User name'
GROUP BY day;

Explanation: This will pull the number of (non-deleted) revisions by a specific user per day. Done /modified (Tool Labs)

Most common templates in a category

SELECT
  tl.tl_title,
  COUNT(*) usages
FROM page p
JOIN categorylinks cl
ON p.page_namespace = 6 -- Optional namespace check.
AND cl.cl_from = p.page_id
AND cl.cl_to = "Files_with_no_machine-readable_license" -- Insert your category here.
JOIN templatelinks tl
ON tl.tl_from = p.page_id
WHERE tl.tl_namespace = 10
GROUP BY tl.tl_title
ORDER BY usages DESC
LIMIT 50;

Explanation: This example selects all templates (in namespace 10) used by pages in a certain namespace (6) and category, groups the results by template name and sorts the template names by frequency.

File description pages without an associated file

SELECT
  page_title
FROM page
WHERE NOT EXISTS (SELECT
                    img_name
                  FROM image
                  WHERE img_name = page_title)
AND NOT EXISTS (SELECT
                  img_name
                FROM commonswiki_f_p.image
                WHERE img_name = page_title)
AND page_namespace = 6
AND page_is_redirect = 0
LIMIT 1000;

Explanation: This will pull file description pages that do not have an associated file, either locally or in the Commons repo. Done /modified (Tool Labs)

Files without an associated file description page

SELECT
  img_name
FROM image
WHERE NOT EXISTS (SELECT
                    page_title
                  FROM page
                  WHERE img_name = page_title
                  AND page_namespace = 6)
AND NOT EXISTS (SELECT
                  page_title
                FROM commonswiki_p.page
                WHERE img_name = page_title
                AND page_namespace = 6);

Explanation: This will pull files that have no associated file description page, either locally or in the Commons repo.

File description pages containing no templates

SELECT
  ns_name,
  page_title,
  page_len
FROM page
JOIN toolserver.namespace
ON dbname = 'enwiki_p'
AND ns_id = page_namespace
LEFT JOIN templatelinks
ON tl_from = page_id
WHERE NOT EXISTS (SELECT
                    img_name
                  FROM commonswiki_p.image
                  WHERE img_name = page_title)
AND page_namespace = 6
AND page_is_redirect = 0
AND tl_from IS NULL
LIMIT 800;

Explanation: This will pull file description pages containing no templates that do not have an associated file description page in the Commons repo.

File description pages containing no templates or categories

SELECT
  ns_name,
  page_title,
  page_len
FROM page
JOIN toolserver.namespace
ON dbname = 'enwiki_p'
AND ns_id = page_namespace
LEFT JOIN templatelinks
ON tl_from = page_id
LEFT JOIN categorylinks
ON cl_from = page_id
WHERE NOT EXISTS (SELECT
                    img_name
                  FROM commonswiki_p.image
                  WHERE img_name = page_title)
AND page_namespace = 6
AND page_is_redirect = 0
AND tl_from IS NULL
AND cl_from IS NULL
LIMIT 800;

Explanation: This will pull file description pages containing no templates or categories that do not have an associated file description page in the Commons repo.

Links on a particular page

SELECT
  pl_namespace,
  pl_title
FROM page
JOIN pagelinks
ON pl_from = page_id
WHERE page_namespace = 0
AND page_title = 'Don\'t_poke_the_bear';

Explanation: This will pull the names of the links on a particular page. For example, the text of the "Don't poke the bear" page contains the link "[[bear]]" so the output of this query will list "Bear" as a result.

Links to a particular page

SELECT
  page_namespace,
  page_title
FROM page
JOIN pagelinks
ON pl_from = page_id
WHERE pl_namespace = 0
AND pl_title = 'Don\'t_poke_the_bear';

Explanation: This will pull the names of the links to a particular page. This is the equivalent of Special:WhatLinksHere. For example, the page "What not to do" may contain the link "[[Don't poke the bear]]"; this query would output "What not to do" as a result.

Pages containing 0 page links

SELECT
  page_namespace,
  page_title
FROM page
LEFT JOIN pagelinks
ON pl_from = page_id
WHERE pl_namespace IS NULL
LIMIT 1000;

Explanation: This will pull pages that contain 0 page links. This will not account for things like image links, template links, category links, or external links. Done (Tool Labs)

Pages with 0 links to them

SELECT
  page_namespace,
  page_title
FROM page
LEFT JOIN pagelinks
ON pl_title = page_title
AND pl_namespace = page_namespace
WHERE pl_namespace IS NULL
LIMIT 1;

Explanation: This will pull pages that have 0 links to them (Special:WhatLinksHere would be empty!).

Short pages with a single author (excluding user pages and redirects)

SELECT
  CONCAT(ns_name, ':', page_title),
  page_len
FROM page
JOIN toolserver.namespace
ON page_namespace = ns_id
AND dbname = 'enwiki_p'
LEFT JOIN templatelinks
ON tl_from = page_id
WHERE page_len < 50
AND page_is_redirect = 0
AND page_namespace NOT IN (2, 3)
AND tl_from IS NULL
AND (SELECT
       COUNT(DISTINCT rev_user_text)
     FROM revision
     WHERE rev_page = page_id) = 1
ORDER BY page_len ASC

Explanation: This will list short pages with a single author (excluding user pages and redirects).

Unused templates

SELECT
  CONCAT('Template:', page_title)
FROM page
LEFT JOIN templatelinks
ON page_namespace = tl_namespace
AND page_title = tl_title
WHERE page_namespace = 10
AND tl_from IS NULL

Explanation: This will pull unused templates.

Unused files in a category

SELECT CONCAT("\n|-\n| [[:File:", img_name, "]] ||", img_user_text, "\n") as UnusedCopyvios
FROM image
JOIN page
ON page_namespace = 6
AND page_title = img_name
JOIN categorylinks
ON cl_from = page_id
AND cl_type = "file"
AND cl_to = "Files_with_no_machine-readable_license"
LEFT JOIN imagelinks
ON il_to = img_name
WHERE il_to IS NULL
GROUP BY img_name;

This query selects all files in the wiki, then joins further tables to determine which are unused by any article and are contained in a specific category, in this case "Files_with_no_machine-readable_license". Other tracking categories can be found at Special:TrackingCategories.

Notice the usage of LEFT JOIN + IS NULL to determine which rows are present in the first table but missing in the second. This is still fast because we first used JOIN to drastically reduce the number of rows involved: categorylinks and imagelinks easily have millions of rows even on a wiki with few thousands pages and files.

Files with a category but not another

Similar to the above, but with multiple category filters: uses multiple LEFT JOIN + IS NULL on the same table. Here the output is formatted for <gallery></gallery> tags.

SELECT CONCAT("File:", img_name, "| ", img_user_text) as UnusedCopyvios
FROM image
JOIN page
ON page_namespace = 6
AND page_title = img_name
JOIN categorylinks
ON cl_from = page_id
AND cl_type = 'file'
AND cl_to = "Files_with_no_machine-readable_author"
LEFT JOIN categorylinks c
ON c.cl_from = page_id
AND c.cl_to = "Files_with_no_machine-readable_source"
WHERE c.cl_to IS NULL
GROUP BY img_name;

Broken image links

SELECT
  CONCAT("* [[", if (page_namespace = 0, page_title, concat(":", ns_name, ":", page_title)), "]]",
              " - [[:Bilde:", il_to, "]]") as links 
FROM imagelinks
JOIN page
ON page_id = il_from 
JOIN toolserver.namespace
ON ns_id = page_namespace
AND dbname = "nowiki_p" 
LEFT JOIN image as I
ON I.img_name = il_to 
LEFT JOIN commonswiki_p.image as J
ON J.img_name = il_to 
WHERE I.img_name IS NULL
AND J.img_name IS NULL;

Explanation: List references to images that are not present locally nor on Commons (nowiki).

Revision counting for specific user till specific time

SELECT
  COUNT(*)
FROM revision
WHERE rev_user_text = 'User name'
AND rev_timestamp < '20120327000000';

Explanation: Return the number of revisions by a specific user up to a certain time.

Log count in recent time by users not in group

A query like this outputs the users with:

• the most log actions of the kind specified (in the example, patrol/patrol minus autopatrolled edits),
• in the interval specified (in the example, last 24 months),
• excluding users in some user groups (in the example, sysop and rollbacker).

/* SLOW_OK adapted from Krinkle, bug 25799 */

SELECT
    count(*) AS counter,
    user_name,
    user_editcount

FROM logging log

JOIN user us
  ON log.log_user = us.user_id

JOIN user_groups ug
  ON log.log_user = ug.ug_user

WHERE ug.ug_user NOT IN
  (SELECT DISTINCT user_groups.ug_user
   FROM user_groups
   WHERE ug_group = 'sysop'
   OR ug_group = 'rollbacker'
  )
AND log.log_type = 'patrol'
AND log.log_action = 'patrol'
AND log.log_timestamp > ( NOW() - INTERVAL 24 MONTH )  
AND ( log.log_params LIKE '%"6::auto";i:0%'
OR    log.log_params LIKE '%\n0' /* not autopatrolled */ )
GROUP BY log.log_user

ORDER BY counter DESC
LIMIT 50;

In wikis where "patrol" right is given to all users, the example query can be used to find active patrollers who would be likely candidates for additional rights (like autropatrolled, rollbacker, sysop etc. depending on the wiki's configuration).

Redirects with more than one revision

SELECT
  page_title,
  rd_namespace,
  rd_title,
  COUNT(*) as edits
FROM revision
INNER JOIN page
ON rev_page = page_id
INNER JOIN redirect
ON rev_page = rd_from
WHERE page_namespace = 0
AND page_is_redirect = 1
GROUP BY page_title
HAVING COUNT(*) > 1;

Explanation: This query lists redirect pages with more than one revision.

Short non-disambiguation pages

SELECT
  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 = 'Disambiguation'
                    AND tl_namespace = 10)
ORDER BY page_len ASC
LIMIT 1000;

Explanation: This query lists 1000 non-disambiguation short pages.

List of external links

SELECT
  COUNT(*),
  TRIM(LEADING 'www.' FROM SUBSTRING_INDEX(SUBSTRING_INDEX(el_to, '/', 3),'/',-1)) AS site
FROM externallinks, page
WHERE el_from = page_id
AND page_namespace = 0
GROUP BY 2
HAVING COUNT(*) > 10 
ORDER BY 1;

Explanation: This query compiles a list of external links (from the main namespace) grouped by website.

List of external links from all wikis

List of external links to a given domain from all namespaces on all Wikimedia projects; requires iterating over wikis, with a bash script in the example, syntax like ./extlinks.sh http://meta.wikimedia.org (outputs DB name, page title, linked URL).

#!/bin/bash
WIKIS=$(mysql -BN -h sql -e 'SELECT dbname FROM toolserver.wiki WHERE domain IS NOT NULL AND is_closed = 0;')
 
for dbname in $WIKIS;
do
        echo $dbname
        echo "
/* SLOW_OK */
SELECT CONCAT(ns_name, ':', page_title), el_to
FROM externallinks
JOIN page ON el_from = page_id
JOIN toolserver.namespacename ON dbname = '$dbname' AND ns_is_favorite AND ns_id = page_namespace
WHERE el_to LIKE '$1%'
-- " | mysql -h ${dbname/_/-}.rrdb.toolserver.org -BcN $dbname
        # mysql switches
        # -c  Prevent comment stripping, need to prevent the query killer
        # -N  No column name heading
        # -B Bare formatted / -t Table formatted / -H HTML output / -X XML output
done;

List of interwiki links from all wikis

As above; use lowercase interwikiki prefix, like ./iwlinks.sh meatball.

#!/bin/bash
WIKIS=$(mysql -BN -h sql -e 'SELECT dbname FROM toolserver.wiki WHERE domain IS NOT NULL AND is_closed = 0;')
 
for dbname in $WIKIS;
do
        echo $dbname
        echo "
/* SLOW_OK */
SELECT CONCAT(ns_name, ':', page_title), CONCAT('$1:', iwl_title)
FROM iwlinks
JOIN page ON iwl_from = page_id
JOIN toolserver.namespacename ON dbname = '$dbname' AND ns_is_favorite AND ns_id = page_namespace
WHERE iwl_prefix = '$1'
-- " | mysql -h ${dbname/_/-}.rrdb.toolserver.org -BcN $dbname
        # mysql switches
        # -c  Prevent comment stripping, need to prevent the query killer
        # -N  No column name heading
        # -B Bare formatted / -t Table formatted / -H HTML output / -X XML output
done;

User activity

Activity of registered users, as edits per day. This query filters out flagged bots but might still miss though.

select 
    rev_user, 
    count(*) / ( unix_timestamp(max(rev_timestamp)) - unix_timestamp(min(rev_timestamp)) ) * 86400.0 as edits_per_day
from revision left join user_groups
on rev_user = ug_user
where (user_id is null or ug_group <> 'bot') and rev_user > 0 
group by rev_user;

Or with some more flexibility to filter users not in group X:

SELECT
    rev_user_text,
    count(*) / ( unix_timestamp(max(rev_timestamp)) - unix_timestamp(min(rev_timestamp)) ) * 86400.0 as edits_per_day
FROM revision
LEFT JOIN user_groups
ON rev_user = ug_user
WHERE ug_user NOT IN
  (SELECT DISTINCT user_groups.ug_user
   FROM user_groups
   WHERE ug_group = 'bot'
   OR ug_group = 'flowbot'
  )
GROUP BY rev_user
ORDER BY edits_per_day DESC
LIMIT 1000;

Article Size

This query works on the WMF Slave db

 SELECT
  rev_page                      AS page_id,
  count(*)                      AS revisions,
  count(distinct rev_user_text) AS editors,
  sum(rev_len)                  AS size
 FROM revision
 GROUP BY rev_page;

Top 10 Articles in Size

This query works on the WMF Slave db

 SELECT
  rev_page,
  count(distinct rev_user_text),
  sum(rev_len)
  page_title as title,
 FROM revision
 INNER JOIN page
  page_id = rev_page
 GROUP BY rev_page
 ORDER BY sum(rev_len) DESC
 LIMIT 10;

Top links, red and blue

This query works on the WMF Slave db

SELECT 
 pl_title, 
 count(*), 
 page_id
FROM enwiki.pagelinks 
LEFT JOIN enwiki.page ON 
 pl_title = page_title 
WHERE 
 pl_namespace = 0 AND
 page_namespace = 0 
GROUP BY 1

First messages to users, by tool/bot used (if any)

SELECT
 page_id, page_title, rev_comment,rev_user, rev_user_text,
 cast(rev_timestamp as datetime) as rev_timestamp, cast(user_registration as datetime) as user_registration,
 datediff(rev_timestamp,user_registration) as datediff_msg_reg, timediff(rev_timestamp,user_registration) as timediff_msg_reg,
 DATE_FORMAT(user_registration,"%Y-%m-%d") as user_reg_ymd, DATE_FORMAT(rev_timestamp,"%Y-%m-%d") as first_msg_ymd, user_id,
 (rev_comment LIKE '%WP:HG%' OR rev_comment LIKE '%WP:HUGGLE%') as huggle, (rev_comment LIKE '%WP:TW%') tw,
 (rev_comment LIKE '%igloo|GLOO]]%') as gloo,
 (rev_comment LIKE '%WP:STiki%') as stiki,
 (rev_user_text LIKE 'ClueBot%') as cluebot,
 (rev_user_text = 'XLinkBot') as xlinkbot,
 (rev_user_text = 'CorenSearchBot') as corenbot,
 (rev_user_text LIKE 'BetacommandBot%') as betacommandbot,
 (rev_user_text LIKE '%Bot') as bot,
 (rev_user_text LIKE page_title) as self
FROM enwiki.page 
LEFT JOIN enwiki.revision ON page_id = rev_page
LEFT JOIN enwiki.user ON replace(page_title,"_"," ") = user_name
WHERE page_namespace = 3 AND LOCATE('/',page_title) = 0 
GROUP BY page_id;

Number of registrations per day

 select DATE_FORMAT(log_timestamp,"%Y-%m-%d"), count(*)
 from enwiki.logging
 where log_action = 'create' AND log_type='newusers'
 group by 1
 order by log_timestamp;

Automated tool and bot edits

UPDATE rev_table SET tool = 'rollback' WHERE rev_comment RLIKE "(Reverted ([0-9]+ )?edits by \[\[Special:Contributions/[^\|]+\|[^]]+\]\] \(\[\[User talk:[^\|]+\|talk\]\]\) to last version by .+)";
UPDATE rev_table SET tool = 'undo' WHERE rc_comment LIKE "Undid revision%";
UPDATE rev_table SET tool = 'huggle' WHERE rc_comment LIKE '%WP:HG%' OR rc_comment LIKE '%WP:HUGGLE%';
UPDATE rev_table SET tool = 'huggle' WHERE rc_comment RLIKE "(Message re\. \[\[[^]]+\]\])|(Level [0-9]+ warning re\. \[\[[^]]+\]\])";
UPDATE rev_table SET tool = 'twinkle' WHERE rc_comment LIKE '%WP:TW%';
UPDATE rev_table SET tool = 'friendly' WHERE rc_comment LIKE '%WP:FRIENDLY%' OR rc_comment LIKE '%WP:Friendly%';
UPDATE rev_table SET tool = 'vandalproof' WHERE rc_comment LIKE '%WP:VPRF%' OR rc_comment LIKE '%WP:VandalProof%' OR rc_comment LIKE '%VandalProof|VandalProof%' OR rc_comment LIKE '%WP:VP2%' OR rc_comment LIKE '%WP:VandalProof%';
UPDATE rev_table SET tool = 'stiki' WHERE rc_comment LIKE '%|STiki]]%';
UPDATE rev_table SET tool = 'npwatcher' WHERE rc_comment LIKE '%|NPWatcher%';
UPDATE rev_table SET tool = 'vandalsniper' WHERE rc_comment LIKE '%|VandalSniper%';
UPDATE rev_table SET tool = 'wikimonitor' WHERE rc_comment LIKE '%m:WikiMonitor%';
UPDATE rev_table SET tool = 'mwt' WHERE rc_comment LIKE '%MWT|MWT]]%';
UPDATE rev_table SET tool = 'awb' WHERE rc_comment LIKE '%AWB|AWB]]%' OR rc_comment LIKE '%AutoWikiBrowser%';
UPDATE rev_table SET tool = 'cluebot' WHERE lower(cast(rc_user_text as CHAR)) LIKE 'cluebot%';
UPDATE rev_table SET tool = 'antivandalbot' WHERE rc_user_text = 'AntiVandalBot';
UPDATE rev_table SET tool = 'orphanbot' WHERE rc_user_text = 'OrphanBot'; 
UPDATE rev_table SET tool = 'pseudobot' WHERE rc_user_text = 'PseudoBot';
UPDATE rev_table SET tool = 'voabot' WHERE rc_user_text LIKE 'VoABot%';
UPDATE rev_table SET tool = 'martinbot' WHERE rc_user_text = 'MartinBot';
UPDATE rev_table SET tool = 'stbot' WHERE rc_user_text LIKE 'STBot%';
UPDATE rev_table SET tool = 'sqlbot' WHERE rc_user_text = 'SQLbot';
UPDATE rev_table SET tool = 'tawkerbot' WHERE rc_user_text LIKE 'Tawkerbot%';
UPDATE rev_table SET tool = 'sinebot' WHERE rc_user_text = 'SineBot';
UPDATE rev_table SET tool = 'csdwarnbot' WHERE rc_user_text = 'CSDWarnBot';
UPDATE rev_table SET tool = 'antispambot' WHERE rc_user_text = 'AntiSpamBot';
UPDATE rev_table SET tool = 'coibot' WHERE rc_user_text = 'COIBot';
UPDATE rev_table SET tool = 'corensearchbot' WHERE rc_user_text = 'CorenSearchBot';
UPDATE rev_table SET tool = 'anomiebot' WHERE rc_user_text = 'AnomieBOT';
UPDATE rev_table SET tool = 'smackbot' WHERE rc_user_text = 'SmackBot';
UPDATE rev_table SET tool = 'xlinkbot' WHERE rc_user_text = 'XLinkBot';
UPDATE rev_table SET tool = 'yobot' WHERE rc_user_text = 'YoBot';
UPDATE rev_table SET tool = 'hostbot' WHERE rc_user_text = 'HostBot';
UPDATE rev_table SET tool = 'mw_message' WHERE rc_user_text = 'MediaWiki message delivery';
UPDATE rev_table SET tool = 'sigmabot' WHERE rc_user_text = 'Lowercase sigmabot III';
UPDATE rev_table SET tool = 'hasteurbot' WHERE rc_user_text = 'HasteurBot';
UPDATE rev_table SET tool = 'legobot' WHERE rc_user_text = 'Legobot';
UPDATE rev_table SET tool = 'dplbot' WHERE rc_user_text = 'DPL bot';
UPDATE rev_table SET tool = 'b-bot' WHERE rc_user_text = 'B-bot';
UPDATE rev_table SET tool = 'suggestbot' WHERE rc_user_text = 'SuggestBot';
UPDATE rev_table SET tool = 'referencebot' WHERE rc_user_text = 'ReferenceBot';
UPDATE rev_table SET tool = 'aaalertbot' WHERE rc_user_text = 'AAlertBot';
UPDATE rev_table SET tool = 'wpcleaner' WHERE rc_comment LIKE "%WP:CLEANER%";
UPDATE rev_table SET tool = 'phantombot' WHERE rc_user_text = 'ThePhantomBot';
UPDATE rev_table SET tool = 'veblenbot' WHERE rc_user_text = 'VeblenBot';
UPDATE rev_table SET tool = 'mediationbot' WHERE rc_user_text = 'MediationBot';
UPDATE rev_table SET tool = 'adminstatsbot' WHERE rc_user_text = 'AdminStatsBot';
UPDATE rev_table SET tool = 'listeriabot' WHERE rc_user_text = 'ListeriaBot';
UPDATE rev_table SET tool = 'musikbot' WHERE rc_user_text = 'MusikBot';
UPDATE rev_table SET tool = 'aaalertbot' WHERE rc_user_text = 'AAlertBot';
UPDATE rev_table SET tool = 'awb-suspected' WHERE (rc_comment LIKE "% awb %" OR rc_comment LIKE "% AWB %") AND tool is NULL;
UPDATE rev_table SET tool = 'awb' WHERE rc_comment LIKE "% via awb %";
UPDATE rev_table SET tool = 'wikilove' WHERE rc_comment LIKE "%new WikiLove message%";
UPDATE rev_table SET tool = 'afch' WHERE rc_comment LIKE '%WP:AFCH%';
UPDATE rev_table SET tool = 'spamublock' WHERE rc_comment LIKE '%spamublock.js%';
UPDATE rev_table SET tool = 'spamuserpage' WHERE rc_comment LIKE '%WP:SUPG|SUPG%';
UPDATE rev_table SET tool = 'csdh' WHERE rc_comment LIKE '%Scripts|CSDH%';
UPDATE rev_table SET tool = 'cat-a-lot' WHERE rc_comment LIKE '%[[Help:Cat-a-lot|Cat-a-lot]]%';
UPDATE rev_table SET tool = 'autoed' WHERE rc_comment LIKE '%|AutoEd]]%';
UPDATE rev_table SET tool = 'refill' WHERE rc_comment LIKE '%[[:en:WP:REFILL|reFill]]%';
UPDATE rev_table SET tool = 'hotcat' WHERE rc_comment LIKE '%WP:HC|HotCat%';
UPDATE rev_table SET tool = 'mosnumscript' WHERE rc_comment LIKE '%WP:MOSNUMscript%';
UPDATE rev_table SET tool = 'reflinks' WHERE rc_comment LIKE '%WP:REFLINKS|Reflinks]%';
UPDATE rev_table SET tool = 'cenpop' WHERE rc_comment LIKE '%via CenPop%';
UPDATE rev_table SET tool = 'ohconfucius' WHERE rc_comment LIKE '%User:Ohconfucius/script|Script%';
UPDATE rev_table SET tool = 'gregu' WHERE rc_comment LIKE '%User:GregU/dashes.js|script%';
UPDATE rev_table SET tool = 'wpadventure' WHERE rc_comment LIKE '%simulated automatically as part of [[WP:The Wikipedia Adventure%';

Determining Huggle and Twinkle messages types by revision comment

UPDATE rev_table SET tool = 'huggle' WHERE rev_comment LIKE '%WP:HG%' OR rev_comment LIKE '%WP:HUGGLE%';
UPDATE rev_table SET tool = 'twinkle' WHERE rev_comment LIKE '%WP:TW%';

UPDATE rev_table SET msg_type = 'warning', msg_type_sub='level1' WHERE rev_comment LIKE 'Message re.%' AND tool = 'huggle';
UPDATE rev_table SET msg_type = 'warning', msg_type_sub='level2' WHERE rev_comment LIKE 'Level 2 warning%' AND tool = 'huggle';
UPDATE rev_table SET msg_type = 'warning', msg_type_sub='level3' WHERE rev_comment LIKE 'Level 3 warning%' AND tool = 'huggle';
UPDATE rev_table SET msg_type = 'warning', msg_type_sub='level4' WHERE rev_comment LIKE 'Level 4 warning%' AND tool = 'huggle';
 
UPDATE rev_table SET msg_type = 'warning', msg_type_sub='level1' WHERE rev_comment LIKE 'General note:%' AND tool = 'twinkle';
UPDATE rev_table SET msg_type = 'warning', msg_type_sub='level2' WHERE rev_comment LIKE 'Caution:%' AND tool = 'twinkle';
UPDATE rev_table SET msg_type = 'warning', msg_type_sub='level3' WHERE rev_comment LIKE 'Warning:%' AND tool = 'twinkle';
UPDATE rev_table SET msg_type = 'warning', msg_type_sub='level4' WHERE rev_comment LIKE 'Final warning:%' AND tool = 'twinkle';
 
UPDATE rev_table SET msg_type = 'deletion', msg_type_sub = 'csd' WHERE rev_comment LIKE 'Notification: speedy deletion%' AND tool = 'twinkle';
UPDATE rev_table SET msg_type = 'deletion', msg_type_sub = 'tag' WHERE rev_comment LIKE 'Notification: tagging for deletion%' AND tool = 'twinkle';
UPDATE rev_table SET msg_type = 'deletion', msg_type_sub = 'prop' WHERE rev_comment LIKE 'Notification: proposed deletion%' AND tool = 'twinkle';
UPDATE rev_table SET msg_type = 'notification', msg_type_sub = 'listing' WHERE rev_comment LIKE 'Notification: listing at%' AND tool = 'twinkle';
UPDATE rev_table SET msg_type = 'talkback' WHERE rev_comment LIKE 'Talkback%' AND tool = 'twinkle';
UPDATE rev_table SET msg_type = 'notification', msg_type_sub = 'blocked' WHERE rev_comment LIKE 'You have been%' AND tool = 'twinkle';
UPDATE rev_table SET msg_type = 'notification', msg_type_sub = 'blocked' WHERE rev_comment LIKE 'Your IP address has been blocked%' AND tool = 'twinkle';
UPDATE rev_table SET msg_type = 'welcome' WHERE rev_comment LIKE 'Added welcome%' AND tool = 'twinkle';
 
UPDATE rev_table SET msg_type = 'welcome' WHERE rev_comment LIKE 'Welcoming%' AND tool = 'huggle';
UPDATE rev_table SET msg_type = 'deletion', msg_type_sub = 'csd' WHERE rev_comment LIKE 'Notification: Speedy deletion%' AND tool = 'huggle';
UPDATE rev_table SET msg_type = 'deletion', msg_type_sub = 'prop' WHERE rev_comment LIKE 'Notification: Proposed deletion%' AND tool = 'huggle';
UPDATE rev_table SET msg_type = 'notification', msg_type_sub = 'blocked' WHERE rev_comment LIKE 'Notification: Blocked%' AND tool = 'huggle';

Deletion count by day (Mainspace)

SELECT
DATE( log_timestamp ) AS Date,
COUNT( log_id ) AS deletions
FROM logging
WHERE log_action='delete'
AND log_namespace =0
GROUP BY Date( log_timestamp )

List of users with a certain log action (not) among global users

For instance, a query to have a list of autocreated accounts (which have an entry in autocreate log) which are not known by CentralAuth (bugzilla:61876):

SELECT
  log_title,
  log_user,
  log_timestamp
FROM logging
JOIN user ON logging.log_action = 'autocreate'
AND log_timestamp > 20130831000000
AND logging.log_user = user.user_id /* user_name and lu_name are normalised, log_* contains e.g. underscores */
WHERE user.user_name NOT IN (
  SELECT lu_name
  FROM centralauth_p.localuser lu
  WHERE lu.lu_wiki = 'metawiki'
);

Editors of a page by edit count

Something like this can be used for instance to quickly list users who (don't) meet requirements for voting in a poll, though usually requirements are about more than edit count (cf. accounteligibility tool for complex examples).

SELECT DISTINCT
  rev_user_text,
  user_editcount
FROM revision
LEFT JOIN user /* This will leave rows for unregistered users too */
ON revision.rev_user = user.user_id
WHERE revision.rev_page = 1000 /* Insert page ID here, e.g. from action=info */
ORDER BY user_editcount DESC;

Frequency of a log action by day

Replace "create" (unregistered user signup) with your log action; and "8) AS DAY" with "6) AS MONTH" and so on for lower resolution.

SELECT
 DAY,
 ACTIONS
 FROM (SELECT
   SUBSTR(log_timestamp, 1, 8) AS DAY,
   SUM(log_action = 'create') AS ACTIONS
   FROM logging
   GROUP BY DAY
   ORDER BY log_id DESC
   LIMIT 30)
   AS logs;

Number of edits prevented by AbuseFilter

SELECT
  SUBSTR(afl_timestamp, 1, 6) AS MONTH,
  COUNT(afl_id) AS edits
FROM abuse_filter_log
WHERE afl_action = 'edit'
AND afl_actions LIKE '%disallow%'
GROUP BY MONTH;

Number of edits hindered by AbuseFilter

SELECT
  SUBSTR(afl_timestamp, 1, 6) AS MONTH,
  COUNT(afl_id) AS edits
FROM abuse_filter_log
WHERE afl_action = 'edit'
AND afl_actions RLIKE '.*(throttle|warn|disallow).*'
GROUP BY MONTH;

Number of users hit by AbuseFilter

SELECT
 MONTH,
 USERS
 FROM (SELECT
   SUBSTR(afl_timestamp, 1, 6) AS MONTH,
   COUNT(DISTINCT(afl_user_text)) AS USERS
   FROM abuse_filter_log
   WHERE afl_actions RLIKE '.*(throttle|warn|disallow|blockautopromote|block|degroup).*'
   GROUP BY MONTH
   ORDER BY afl_id ASC)
   AS logs;

Edits per month with given tag and editcount

In the example, VisualEditor edits by users with a (current!) edit count of at least 1000 edits.

SELECT
  SUBSTR(rev_timestamp, 1, 6) AS MONTH,
  COUNT(rev_id) AS edits
FROM revision
JOIN change_tag ON ct_rev_id = rev_id
JOIN user ON rev_user = user_id
WHERE ct_tag = 'visualeditor'
AND user_editcount > 1000
GROUP BY MONTH;

Edits per month with two given tags

In the example, VisualEditor edits tagged "nowiki" (by AbuseFilter).

SELECT
  SUBSTR(rev_timestamp, 1, 6) AS MONTH,
  COUNT(rev_id) AS edits
FROM change_tag as a
JOIN change_tag as b ON a.ct_rev_id = b.ct_rev_id
JOIN revision ON rev_id = a.ct_rev_id
WHERE a.ct_tag = 'nowiki'
AND b.ct_tag = 'visualeditor'
GROUP BY MONTH;

Survived editors

Editor retention by month considering time between first and last edit, excluding bots, ordered by month of first edit.

SELECT
 CONCAT(month, '/', year) monthly,
 SUM(period < 7) less_than_1week,
 SUM(period BETWEEN 7 AND 30) 1week_to_1month,
 SUM(period BETWEEN 31 AND 365) 1month_to_1year,
 SUM(period > 365) more_than_1year
 FROM (
  SELECT
   SUBSTR(first, 5, 2) month,
   SUBSTR(first, 1, 4) year,
   TIMESTAMPDIFF(DAY, first, last) period
   FROM (
    SELECT
     MIN(rev_timestamp) first,
     MAX(rev_timestamp) last
     FROM revision_userindex
     WHERE rev_user != 0 AND rev_user NOT IN (SELECT ug_user FROM user_groups WHERE ug_group = 'bot')
     GROUP BY rev_user
   ) a
 ) b
 GROUP BY monthly
 ORDER BY year, month;

Top most active editors under some conditions

For instance the following gives the top 50 most active translators (editors of the "Translations" namespace, where available). A simple JOIN should give most of the filtering options you'll need.

SELECT
  rev_user_text, COUNT(*)
FROM revision
JOIN page
ON rev_page = page_id
WHERE page_namespace = 1198
/* Other filters like page suffix and year
AND page_title LIKE '%/it'
AND rev_timestamp LIKE '2014%' */
GROUP BY rev_user_text
ORDER BY COUNT(*) DESC
LIMIT 50;

Or, if you want to count log actions and filter e.g. by current user group, in the example number of deletions by sysops:

SELECT
  log_user_text, COUNT(*)
FROM logging
JOIN user_groups ON ug_user = log_user
WHERE log_type = 'delete'/*
AND log_action = 'delete'
AND log_timestamp > 20130101000000*/
AND ug_group = 'sysop'
GROUP BY log_user_text
ORDER BY COUNT(*) DESC
LIMIT 50;

Regular expressions

Is an IPv4 address

This query matches 999.999.999.999 as well as valid addresses like 129.47.61.5, but not IPv6 addresses.

REGEXP '^[0-9]{1,3}[.][0-9]{1,3}[.][0-9]{1,3}[.][0-9]{1,3}$'

Is an IPv4 or IPv6 address

This realistically matches only IP addresses, but doesn't check the 8th IPv6 group. Note that, for a brief time, MediaWiki allowed users to register with IP-looking usernames.

REGEXP '(^([0-9]{1,3}[.]){3}[0-9]{1,3}$|^([[:alnum:]]{1,4}[:]){7})'

Most edited pages in last month

SELECT rc_title, count(*) as num_edits
FROM recentchanges 
WHERE rc_namespace = 0 
GROUP BY 1 ORDER BY 2 DESC
LIMIT 25;

See also

• Database meta_p