ページ、ブログ投稿、添付ファイルの数の確認方法
目的
各スペースに存在するページの数や、1 つの親ページに関連付けられている子ページ、添付ファイル、およびコメントの数を確認したい場合があります。
The following sql queries can help identify spaces and parent pages that are getting too large and should bit split up to improve performance in the instance. Do note that you may need to modify these queries for specific databases.
ソリューション
特定の親ページの子ページ、添付ファイル、コメントの数
drop table if exists tempdata;
create table tempdata as
(select contentid from content where
contentid in (select contentid from content where lowertitle = '<the-page-title-lowercased>') or
parentid in (select contentid from content where lowertitle = '<the-page-title-lowercased>'));
select count(*), contenttype from content where
pageid in (select contentid from tempdata)or
contentid in (select contentid from tempdata) group by contenttype;
注: これらのクエリは Postgres 用に作成されているため、他の種類のデータベースでは調整が必要な場合があります。
1 つのスペース内のページの数
select count(CONTENTID) as "number of pages", SPACES.SPACENAME from CONTENT
join SPACES on CONTENT.SPACEID = SPACES.SPACEID
where CONTENT.SPACEID is not null
and CONTENT.PREVVER is null
and CONTENT.CONTENTTYPE = 'PAGE'
and CONTENT.CONTENT_STATUS='current'
group by SPACES.SPACENAME
order by "number of pages" desc;1 つのスペース内のブログ投稿の数
select count(CONTENTID) as "number of pages", SPACES.SPACENAME from CONTENT
join SPACES on CONTENT.SPACEID = SPACES.SPACEID
where CONTENT.SPACEID is not null
and CONTENT.PREVVER is null
and CONTENT.CONTENTTYPE = 'BLOGPOST'
and CONTENT.CONTENT_STATUS='current'
group by SPACES.SPACENAME
order by "number of pages" desc;インスタンス全体でのページの数
select count(*) from content
where contenttype='PAGE'
and prevver is null
and content_status='current';インスタンス全体でのブログ投稿の数
select count(*) from content
where contenttype='BLOGPOST'
and prevver is null
and content_status='current';インスタンス全体でのコメントの数
select count(*) from content
where contenttype='COMMENT'
and prevver is null
and content_status='current';インスタンス全体での添付ファイルの数
select count(*) from content
where contenttype='ATTACHMENT'
and prevver is null
and content_status='current';Total number of spaces in Confluence:
select count(spacename) from spaces;The number of attachments in a space: This count will include deleted attachments if not purged from space tools
5.7 よりも前
select count(ATTACHMENTID) as "number of attachments", SPACES.SPACENAME from CONTENT
join SPACES on CONTENT.SPACEID = SPACES.SPACEID
join ATTACHMENTS on ATTACHMENTS.PAGEID = CONTENT.CONTENTID
where CONTENT.SPACEID is not null
group by SPACES.SPACENAME
order by "number of attachments" desc;5.7 よりもあと
select count(CONTENTID) as "number of attachments", SPACES.SPACENAME from CONTENT
join SPACES on CONTENT.SPACEID = SPACES.SPACEID
where CONTENT.SPACEID is not null
and CONTENT.PREVVER is null
and CONTENT.CONTENTTYPE = 'ATTACHMENT'
group by SPACES.SPACENAME
order by "number of attachments" desc;
最終更新日: 2023 年 1 月 30 日
Powered by Confluence and Scroll Viewport.