Wikipedia talk:Request a query

Nice idea

Hi Edgars2007 and Izno. This page is a nice idea. It'll hopefully reduce some pressure from Wikipedia talk:Database reports. Do you think centralizing requests like this on a page like m:Tech might be better? Developer/programmer/coder/tech people knowledge is pretty finite, and many of the problems we encounter here on the English Wikipedia have applicable solutions for other wikis, so maybe a centralized forum would be better? m:Tech was established as a sort of cross-wiki technical village pump. m:Tech also houses m:Tech/News and has an associated IRC channel that people sometimes contribute to (#wikimedia-tech on freenode). --MZMcBride (talk) 20:12, 14 January 2017 (UTC)

@MZMcBride: We regularly see this kind of request at both WP:VPT and WP:Bot requests. It seems natural to provide an explicit forum for people to request one-off queries. The Wikidata equivalent has been pretty successful. I have no idea whether this will be also, since the denizens here seem somewhat less gnomish/automation driven compared to WD... --Izno (talk) 22:32, 14 January 2017 (UTC)

I completely agree that people make these requests a lot. I've fulfilled plenty of these types of requests. An explicit forum is fine, but I'm worried that we have too many explicit forums. :-)

With all of these technical forums, there are also recurring concerns with people posting "XY problems" in which they make very specific requests seeking an attempted solution instead of explaining/describing their actual problem (cf. <https://meta.stackoverflow.com/a/66378>). (Hmmm, a link to Stack Overflow, another technical forum.) I'm as guilty of posting XY problems as anyone. --MZMcBride (talk) 18:20, 15 January 2017 (UTC)

Table-schema description

It would be useful to have information about the schemas of the tables available so that we can decide what is reasonable to ask (or how to make a reasonable request to figure out what we want). DMacks (talk) 03:47, 7 May 2017 (UTC)

@DMacks: I have added links to the table schemas here. --Bamyers99 (talk) 15:40, 7 May 2017 (UTC)

I created a tips page

Well, more specifically, I moved the old Wikipedia:Request a query/Schemas page to Wikipedia:Request a query/Tips and schemas and added a bunch of content. I added all the tips and notes I jotted down over the last month. I invite WP:QUERY regulars to take a look at it, edit it, and hopefully add your tips and tricks as well. Pinging some tech savvy people: @Cryptic, Firefly, and Chlod: Thanks and hope you like the changes. –Novem Linguae (talk) 12:31, 15 April 2021 (UTC)

Quarry timeout? Replica database age?

Two hopefully simple questions. 1) What's the timeout number for Quarry? 60 minutes? 2) How many days/weeks do the replica SQL databases lag behind the production database? Thanks. –Novem Linguae (talk) 06:35, 22 April 2021 (UTC)

I wrote this query to see the replica database age. I'm getting around 3 seconds. Looks like it's kept almost completely in sync. That's pretty cool. –Novem Linguae (talk) 07:07, 22 April 2021 (UTC)

Coming back 2 years later and 2 years smarter, here's a better way to check replag: https://replag.toolforge.org/ –Novem Linguae (talk) 22:59, 16 June 2023 (UTC)

New linktarget table

Watchers of this page should be aware of the new linktarget table, which has replaced the (I guess soon-to-be-dropped?) tl_namespace and tl_title columns in templatelinks, and will eventually do the same with the analogous columns in pagelinks, categorylinks, and imagelinks. Illustrative query. Apparently this was announced in March. —Cryptic 01:57, 15 September 2022 (UTC)

Here's hoping performance remains reasonable. The March announcement says that pagelinks, imagelinks and categorylinks "will follow" and that "We will announce any major changes beforehand". Does anyone know where such announcements appear? That'll be a lot of queries to amend. Certes (talk) 11:01, 15 September 2022 (UTC)

I guess just on wikitech-l. I was surprised this didn't appear in Tech News, given that the relatively trivial change to site_stats did and I seem to recall the schema change for actor did too. Or you can subscribe to the phab tickets, now that we know they exist. —Cryptic 12:02, 15 September 2022 (UTC)

Good point. I've dropped a note at m:Talk:Tech/News. Certes (talk) 12:32, 15 September 2022 (UTC)

Also relevant: phab:T299947 for pagelinks. I just ran an old query, which currently works both with both old and new table layouts. The new version seems slightly faster, though caching and varying loads foil an exact comparison. Certes (talk) 12:51, 15 September 2022 (UTC)

For now, anyway. Like actor and comment, the linktarget view hides a query against templatelinks that may eventually prove problematic. Unlike actor and comment, there aren't any alternate views - not that I'd expect there to be any quite yet, since it's only been rolled out to templatelinks. —Cryptic 13:09, 15 September 2022 (UTC)

Ah... I didn't realise linktarget was a temporary implementation as a view (though that makes much more sense than trying to maintain two copies of the data). Of course, it will perform differently when it becomes an actual table. Certes (talk) 13:23, 15 September 2022 (UTC)

T299947 now seems to be making progress. When complete, this will break most Quarry queries which use pagelinks. Certes (talk) 19:21, 6 September 2023 (UTC)

Database report template

{{Database report}} template can now be used to set up one-off or periodically updating reports in userspace or project namespace, given an SQL query. The template doc lists the supported formatting options. Feel free to give it a try and let me know if you face any issues. – SD0001 (talk) 15:44, 28 October 2022 (UTC)

Thank you! That looks very useful and flexible. The Toolforge SQL Optimizer is handy for tuning more complex queries. Certes (talk) 17:20, 28 October 2022 (UTC)

Oooh, I like this a lot. Looks very scalable. Great work! –Novem Linguae (talk) 21:08, 28 October 2022 (UTC)