Wikipedia:Request a query

This is a page for requesting one-off database queries for certain criteria. Users who are interested and able to perform SQL queries on the projects can provide results from the Quarry website.

You may also be interested in the following:

• If you are interested in writing SQL queries or helping out here, visit our tips page.
• If you need to obtain a list of pages that meet certain criteria, consider using PetScan (user manual) or the default search. PetScan can generate lists of articles in subcategories, articles which transclude some template, etc.
• If you need to make changes to a number of articles based on a particular query, you can post to the bot requests page, depending on how many changes are needed.
• For long-term review and checking, database reports are available.

The database replicas do not have access to page content, so queries which require checking wikitext cannot be answered with database queries. In particular, there is no way to query for template parameters or anything related to references. However, someone may be able to assist by querying in another way (for example, checking for external links rather than references) or suggest an alternative tool.

Wikipedia:Database reports/Long stubs takes too long to run

The report at Wikipedia:Database reports/Long stubs is taking between 500 and 600+ seconds to run. When it takes longer than 600 seconds, the bot that updates the page times out and fails to update the page. The bot operator suggested that we come here for help with making the query more efficient. I tried limiting the output to 500 pages instead of 1,000 pages, but I have a feeling that cutoff takes milliseconds to apply after the hard work of the query actually runs. – Jonesey95 (talk) 15:55, 14 July 2025 (UTC)

It was falling back on the page_len index again, probably because of the schema change to use linktarget in categorylinks instead of storing text in cl_to. The check for and sort on length fools the optimizer into looking at pages in order of decreasing size and then filtering for the ones in the stub categories; that's no good here because most stubs tend to be small, so it was always looking through the majority of the 63.5 million total pages before finding the 2.3 million stubs in them.

The other issue is that it's processing pages multiple times, once for each stub category it's in, rather than just using Category:All stub articles. All well-formed stub templates also categorize their pages into that. (There are some ill-formed stub templates, and sometimes you see pages directly categorized into a stub category without using the template.)

I've shuffled things around so it's using the category-first query plan again (for now, at least) to fix the first problem, and "fixed" the second. This is always going to be a slow query, though; there's no getting around having to look at each of those 2.3 million pages. You were right in your milliseconds estimate about the output limit. —Cryptic 16:53, 14 July 2025 (UTC)

Thanks so much. The combination of the database structure and SQL query language is above my pay grade. – Jonesey95 (talk) 13:53, 15 July 2025 (UTC)

User edits by namespace

I wanted to see how many edits I had made in Wikipedia pages in the last 90 days, broken down by namespace. I submitted the following query to Quarry several times in the last couple of weeks, and it always timed out after 30--40 minutes
SELECT COUNT(*), page_namespace FROM actor INNER JOIN revision ON (rev_actor = actor_id) INNER JOIN page ON (page_id = rev_page) WHERE actor_name = 'Ion Soggo' AND rev_timestamp > NOW() - 90 GROUP BY page_namespace;
Is there a better way to get the results? I will appreciate any suggestions.
Ion Soggo (talk) 02:30, 29 July 2025 (UTC)

Ion Soggo Try this. Stefen 𝕋ower_{s among the rest!} ^{Gab • Gruntwerk} 02:59, 29 July 2025 (UTC)

StefenTower, thank you for your help. The improvement in the query performance is amazing. It shows I still need to learn a lot of the MySQL version of SQL.

Ion Soggo (talk) 15:08, 29 July 2025 (UTC)

You're welcome. For future reference, they use MariaDB SQL which has many similarities to MySQL. Stefen 𝕋ower_{s among the rest!} ^{Gab • Gruntwerk} 17:44, 29 July 2025 (UTC)

Searching for pages with edits by Rambot that contain *both* of two edit summary types

Hello, I would like to have a list of page titles (almost certainly all redirects relating to US places) edited by Rambot that contain *both* of the following edit summaries in the same page history, the first being "Added geographic and demographic data" and the second starting with "Moved article to". Back in the early days, Rambot did several hundred cut-and-paste moves; most have been repaired by now, but I just found a few more relating to Massachusetts towns like Lenox, Massachusetts, and was wondering if there were any more in other US states. A live example which I haven't history-merged yet because it's already way past my bedtime is in the page history at Hopkinton (town), Middlesex County, MassachusettsHopkinton (town), Middlesex County, Massachusetts. The history at such examples is relatively trivial, but often leads to other more substantial history that needs to be merged. Thanks for your help. Graham87 (talk) 19:05, 14 August 2025 (UTC)

quarry:query/96317. —Cryptic 19:32, 14 August 2025 (UTC)

Editors who would become extended confirmed upon their next edit

For statistical purposes, I'm wondering if I could get a count of editors who would become extended confirmed upon their next edit? That is, editors with 500 edits, account age >30 days, and are not/have never previously been extended confirmed? Best, HouseBlaster (talk • he/they) 02:03, 17 August 2025 (UTC)

122. List at quarry:query/96352. —Cryptic 02:21, 17 August 2025 (UTC)

Would editors with more than 500 edits also qualify? How about those with exactly 499 edits (assuming that no pages they have edited get deleted before their next edit)? Certes (talk) 10:41, 17 August 2025 (UTC)

More than 500 might, if they hadn't edited since before extendedconfirmed existed; I don't know offhand whether qualified users were promoted en masse at the time, and can see arguments either way for whether they should have. Will investigate later. No, also ones who made their 501st edit before their account was 30 days old, but none since. So, yeah, likely lots.

499 edits wouldn't, since autopromotion happens on the 501st edit. Deletion doesn't matter either, since user_editcount is only incremented when you make an edit, and not ever decremented. —Cryptic 12:04, 17 August 2025 (UTC)

So, looking like 27736, unless I missed something else. The first autopromotions happened at 23:17, 5 April 2016. (That seems way late to me - I'd have guessed closer to 2010 - but it turns out to be right.) 903 of these accounts have edited since then (page ten of quarry:query/96352), so 26833 are entirely left over from before the group existed. —Cryptic 12:34, 17 August 2025 (UTC)

Thanks, Cryptic and Certes :) HouseBlaster (talk • he/they) 16:25, 17 August 2025 (UTC)