Use a large temporary file for sorting when clicking Downloads on the My Transfers screen

[makinog3]

When there are many transfer histories, very large files are needed to sort by Downloads (date) on the My Transfers screen.
In our site's case, it needs over 1 GB. And it takes more than 30 seconds to display.
It is not related to the number of transfers displayed on My Transfers screen, but depends on the number of total transfers in the Transfers/AuditLogs tables on MySQL database.

• Number of rows in Transfers table;
  about 40,000

• Number of rows in AuditLogs table;
  about 800,000

• SQL

SELECT * FROM transfersauditlogsdlcountview  WHERE userid = '105' AND status='available' and ( guest_id is null or guest_transfer_shown_to_user_who_invited_guest )  ORDER BY  count DESC, created DESC  LIMIT 16

[monkeyiq]

Could you please post the output of an EXPLAIN on this query.

The query uses transfersauditlogsdlcountview which is the first shown below. That view in turn uses transfersauditlogsdlsubselectcountview which is shown below it.

EXPLAIN select t.*,count from Transfers  t 
  left outer join transfersauditlogsdlsubselectcountview zz 
  on t.id = zz.id 

EXPLAIN select id,count(*) as count from transfersauditlogsview where  
  ( event = 'download_ended' or event = 'archive_download_ended' ) group by id

I have a feeling that forcing a limit in the query on userid first and a date range might unlock the expected performance. I'll have a think about what that SQL might look like for a subsequent reply.

[makinog3]

Thank you for your response.
We use MariaDB 5.5

MariaDB [filesender]> EXPLAIN SELECT * FROM transfersauditlogsdlcountview  WHERE userid = '105' AND status='available' and ( guest_id is null or guest_transfer_shown_to_user_who_invited_guest )  ORDER BY  count DESC, created DESC  LIMIT 16;
+------+--------------+------------+------+-------------------+-------------------+---------+-----------------+--------+----------------------------------------------+
| id   | select_type  | table      | type | possible_keys     | key               | key_len | ref             | rows   | Extra                                        |
+------+--------------+------------+------+-------------------+-------------------+---------+-----------------+--------+----------------------------------------------+
|    1 | SIMPLE       | t          | ref  | Transfers_userid  | Transfers_userid  | 8       | const           |     13 | Using where; Using temporary; Using filesort |
|    1 | SIMPLE       | <derived3> | ref  | key0              | key0              | 9       | filesender.t.id |     10 |                                              |
|    3 | DERIVED      | <derived4> | ALL  | NULL              | NULL              | NULL    | NULL            | 223926 | Using where; Using temporary; Using filesort |
|    4 | DERIVED      | t          | ALL  | NULL              | NULL              | NULL    | NULL            |  37321 |                                              |
|    4 | DERIVED      | a          | ref  | AuditLogs_Type_ID | AuditLogs_Type_ID | 1532    | const,func      |      3 | Using where                                  |
|    5 | UNION        | t          | ALL  | PRIMARY           | NULL              | NULL    | NULL            |  37321 |                                              |
|    5 | UNION        | f          | ref  | Files_transfer_id | Files_transfer_id | 8       | filesender.t.id |      1 | Using index                                  |
|    5 | UNION        | a          | ref  | AuditLogs_Type_ID | AuditLogs_Type_ID | 1532    | const,func      |      3 | Using where                                  |
| NULL | UNION RESULT | <union4,5> | ALL  | NULL              | NULL              | NULL    | NULL            |   NULL |                                              |
+------+--------------+------------+------+-------------------+-------------------+---------+-----------------+--------+----------------------------------------------+
9 rows in set (0.02 sec)

[makinog3]

@monkeyiq I want to try "help sort by downloads by better recording the count".
How will I add the download_count column on Transfers table?

[monkeyiq]

The scripts/upgrade/database.php should notice that the table schema has changed and add the download_count column to the database for you. After you have the column you may want to then run scripts/upgrade/explicit/upgrade-2.48-to-2.49-post-download-count.php if you want to try to populate that column with real data.

This upgrade-2.48-to-2.49-post-download-count.php script will likely be very slow though as it does a similar query to what clicking on the download sort would do for every transaction. On the other hand, you should be able to not run the post-download-count.php script and it will default to zero. This new download_count is only used when sorting the table at the moment, so that is the only place the data being zero should effect the user. This will give incorrect results for people for a while until the download numbers naturally are updated in the database table as new downloads occur. You could test by making some new downloads and click to sort by downloads to see that it is moved to the top.

You will need to have either a patched filesender or pull git development to get the new code.

[makinog3]

I see. I wondered why the scripts/upgrade/database.php has not updated this commit, but I understood the script would create the alter command automatically.
I will try it later. Thank you very much.