Sometimes, when talking about Xray attachments, it can be helpful to understand what is stored in Xray datatables and their size.
With this simple SQL query, is possible to know the size of attachments by project:
SELECT COUNT(*) AS num_attachments, AVG(ao.FILE_SIZE / 1000000) AS avg_filesize_Mb, SUM(ao.FILE_SIZE / 1000000) AS total_filesize_Mb, p.pname, p.pkey FROM project p JOIN jiraissue j ON p.id = j.project JOIN AO_8B1069_ATTACHMENT ao ON ao.ISSUE_ID = j.id GROUP BY p.pkey, p.pname;
SQL for all projects:
SELECT COUNT(*) AS num_attachments, SUM(file_size / (1024 * 1024)) AS total_size_MB FROM AO_8B1069_ATTACHMENT;
SQL to know the size of each individual attachment by project:
SELECT ao.FILE_NAME, ao.FILE_SIZE / 1000000 AS filesize_Mb, p.pname, p.pkey FROM project p JOIN jiraissue j ON p.id = j.project JOIN AO_8B1069_ATTACHMENT ao ON ao.ISSUE_ID = j.id;