Some queries are currently not runnable on s5.labsdb because of performance problems. Probably this is caused by federated tables.
Either these queries have not finished after hours and i kill them or i am getting errors caused by out of memory (lost connection, Table './mysql/proc' is marked as crashed)
I have many many complex queries in my scripts using commonswiki.
Here is one example query my bot runs every four hours:
It returns a list of files requested for deletions on commons with a link to deletion talk page section which are used on dewiki presentation namespace and the deletion request page was modified within the last two weeks by a human.
(the "used on dewiki" is not 100% correct because of performance reason i am using the big globalimagelinks in a second query, but that shouldn't matter here - only if sb. wants to copy the query for his own tools)
on WMDE toolserver
SELECT 'commonswiki', cp.page_id,
CONCAT('[[:Datei:',REPLACE(cp.page_title,'_',' '),']]<small> [[:commons:Commons:Deletion requests/',REPLACE(SUBSTRING(cl_sortkey_prefix,LOCATE('#',cl_sortkey_prefix)+1),'_',' '),'|CDR]]</small>')
FROM commonswiki_p.templatelinks
INNER JOIN commonswiki_p.page cp ON tl_from = cp.page_id
INNER JOIN commonswiki_p.recentchanges ON rc_cur_id = cp.page_id AND rc_bot=0
INNER JOIN dewiki_p.imagelinks ON il_to = cp.page_title
INNER JOIN dewiki_p.page dp ON il_from = dp.page_id
INNER JOIN commonswiki_p.categorylinks ON cp.page_id=cl_from AND cl_to LIKE 'Deletion_requests_%'
WHERE tl_namespace=10 AND tl_title='Delete' AND cp.page_namespace=6
AND DATEDIFF(NOW(), rc_timestamp) < 14
AND dp.page_namespace IN (0,6,10,14,100) AND cl_sortkey_prefix LIKE '%#%'
GROUP BY cp.page_id
ORDER BY NULL;Result ON TS: 85 rows in set (0.25 sec)
Query for wmf Labs:
SELECT 'commonswiki', cp.page_id,
CONCAT('[[:Datei:',REPLACE(cp.page_title,'_',' '),']]<small> [[:commons:Commons:Deletion requests/',REPLACE(SUBSTRING(cl_sortkey_prefix,LOCATE('#',cl_sortkey_prefix)+1),'_',' '),'|CDR]]</small>')
FROM commonswiki_f_p.templatelinks
INNER JOIN commonswiki_f_p.page cp ON tl_from = cp.page_id
INNER JOIN commonswiki_f_p.recentchanges ON rc_cur_id = cp.page_id AND rc_bot=0
INNER JOIN dewiki_p.imagelinks ON il_to = cp.page_title
INNER JOIN dewiki_p.page dp ON il_from = dp.page_id
INNER JOIN commonswiki_f_p.categorylinks ON cp.page_id=cl_from AND cl_to LIKE 'Deletion_requests_%'
WHERE tl_namespace=10 AND tl_title='Delete' AND cp.page_namespace=6
AND DATEDIFF(NOW(), rc_timestamp) < 14
AND dp.page_namespace IN (0,6,10,14,100) AND cl_sortkey_prefix LIKE '%#%'
GROUP BY cp.page_id
ORDER BY NULL;aborted by me after 3 hours.
Please optimize database performance for queries joining with commonswiki.
Version: unspecified
Severity: normal