select 
   count(*),
   year(r.rev_timestamp) as 'year',
   month(r.rev_timestamp) as 'month',
   day(r.rev_timestamp) as 'day',
   hour(r.rev_timestamp) as 'hour',
   convert(u.user_name using utf8) as 'user'
   from revision r inner join user u on r.rev_user=u.user_id 
   where r.rev_timestamp>'202007' 
   group by 2,3,4,5,6
   order by 2 desc, 3 desc, 4 desc, 5 desc

select r.rev_timestamp,convert(u.user_name using utf8) as t 
   from revision r inner join user u on r.rev_user=u.user_id 
   where r.rev_timestamp>'202008' 
   order by r.rev_timestamp desc;

select count(*),u.user_id,convert(u.user_name using utf8) as username
from revision r
inner join user u
on r.rev_user=u.user_id
group by 2
order by 1 desc

describe externallinks
Field	Type	Null	Key	Default	Extra
el_id	int(10) unsigned	NO	PRI	(null)	auto_increment
el_from	int(10) unsigned	NO	MUL	0	
el_to	blob	NO	MUL	(null)	
el_index	blob	NO	MUL	(null)

select 
   count(*),
   year(r.rev_timestamp) as 'year',
   month(r.rev_timestamp) as 'month',
   day(r.rev_timestamp) as 'day',
   hour(r.rev_timestamp) as 'hour',
   convert(u.user_name using utf8) as 'user'
   from revision r inner join user u on r.rev_user=u.user_id 
   where r.rev_timestamp>'202007' 
   group by 2,3,4,5,6
   order by 2 desc, 3 desc, 4 desc, 5 desc

select r.rev_timestamp,convert(u.user_name using utf8) as t 
   from revision r inner join user u on r.rev_user=u.user_id 
   where r.rev_timestamp>'202008' 
   order by r.rev_timestamp desc;

select count(*),u.user_id,convert(u.user_name using utf8) as username
from revision r
inner join user u
on r.rev_user=u.user_id
group by 2
order by 1 desc

describe externallinks
Field	Type	Null	Key	Default	Extra
el_id	int(10) unsigned	NO	PRI	(null)	auto_increment
el_from	int(10) unsigned	NO	MUL	0	
el_to	blob	NO	MUL	(null)	
el_index	blob	NO	MUL	(null)

select 
  convert(user_name using utf8) as name,
  convert(user_touched using utf8) as time,
  user_editcount 
from user 
order by 2 desc

name	time	user_editcount
Wf	20191009122047	689

select count(*),u.user_id,convert(u.user_name using utf8) as username
from revision r
inner join user u
on r.rev_user=u.user_id
group by 2
order by 1 desc

delete from user where not user_id in (1)

select 
  convert(old_text using utf8) as t 
from text

select 
  convert(u.user_name using utf8) as username,
  p.page_id,
  convert(p.page_title using utf8) as pagetitle,
  r.rev_user as userid,
  convert(t.old_text using utf8) as text
from page p
inner join revision r
  on p.page_id=r.rev_page
inner join user u
  on r.rev_user=u.user_id  
inner join text t
  on r.rev_text_id=t.old_id

select count(*) as textcount from text where old_id in (select rev_text_id from revision where not rev_user in (1)); 
set autocommit=0;
start transaction;
delete from text where old_id in (select rev_text_id from revision where not rev_user in (1)) ; 
commit;

select count(*) as textcount from text where old_id in (select rev_text_id from revision where not rev_user in (1));

delete
from text
where old_id in (select rev_text_id from revision where rev_user!=1)

optimize table text