Dev {Tricks}

Query to find list of email domains

SELECT substring_index(email, '@', -1) domain, COUNT(*) email_count FROM users GROUP BY substring_index(email, '@', -1) -- If you want to sort as well: ORDER BY email_count DESC, domain;

Query to describe user activity in order to identify suspect users:

select
	u.user_id,
	u.username,
	u.email,
	u.first_name,
	u.middle_name,
	u.last_name,
	u.url,
	u.disabled,
	u.date_registered,
	u.date_validated,
	bit_or(r.role_id) roles,
	count(distinct a.article_id) articles,
	count(distinct e.article_id) editing,
	count(distinct v.review_id) reviews,
	group_concat(b.setting_value ORDER BY b.setting_value separator ' ') biography,
	group_concat(c.body ORDER BY c.body separator ' ') comments,
    group_concat(s.setting_value ORDER BY s.setting_value separator ' ') signature,
	count(distinct c.comment_id) comment_count
from
	users u
	left outer join user_settings b on (u.user_id = b.user_id and b.setting_name = 'biography')
	left outer join user_settings s on (u.user_id = s.user_id and s.setting_name = 'signature')
	left outer join comments c on (u.user_id = c.user_id)
	left outer join roles r on (u.user_id = r.user_id)
	left outer join articles a on (u.user_id = a.user_id and a.status in (1, 3))
	left outer join review_assignments v on (u.user_id = v.reviewer_id)
	left outer join edit_assignments e on (u.user_id = e.editor_id)
group by
	u.user_id,
	u.username,
	u.email,
	u.first_name,
	u.middle_name,
	u.last_name,
	u.url,
	u.disabled,
	u.date_registered,
	u.date_validated;

There may have some gallery that don’t have files property attached. Try running in your database…

SELECT sg.galley_id, sg.file_id FROM publication_galleys sg LEFT JOIN submission_files sf ON (sf.file_id = sg.file_id) WHERE sf.file_id IS NULL;

You have to review the return values. There may be incorrect file ID.