[
https://issues.apache.org/jira/browse/AIRAVATA-3583?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17497036#comment-17497036
]
Marcus Christie commented on AIRAVATA-3583:
-------------------------------------------
Adding the following index seems to have fixed the issue:
{code:sql}
create index airavata_django_portal_sdk_userfiles_file_path on
airavata_django_portal_sdk_userfiles (file_path(255), username);
{code}
I'll see if I can add this to a data migration.
> slow airavata_django_portal_sdk_userfiles query
> -----------------------------------------------
>
> Key: AIRAVATA-3583
> URL: https://issues.apache.org/jira/browse/AIRAVATA-3583
> Project: Airavata
> Issue Type: Bug
> Components: Django Portal
> Reporter: Marcus Christie
> Assignee: Marcus Christie
> Priority: Major
>
> Queries for existing data products in the Django Portal are taking longer
> than 10 seconds for searchsra.
> Example from slow query log:
> {noformat}
> # Time: 220223 15:41:46
> # User@Host: django[django] @ gridfarm004.ucs.indiana.edu [156.56.104.84]
> # Thread_id: 3185092 Schema: django_searchsra QC_hit: No
> # Query_time: 10.402407 Lock_time: 0.000057 Rows_sent: 0 Rows_examined:
> 57036
> SET timestamp=1645648906;
> SELECT (1) AS `a` FROM `airavata_django_portal_sdk_userfiles` WHERE
> (`airavata_django_portal_sdk_userfiles`.`file_path` =
> '/var/www/portals/gateway-user-data/searching-sra/--REDACTED--' AND
> `airavata_django_portal_sdk_userfiles`.`file_resource_id` =
> 'gf4.ucs.indiana.edu_61552681-96f0-462a-a36c-a62a010bffc6' AND
> `airavata_django_portal_sdk_userfiles`.`username` = '--REDACTED--') LIMIT 1;
> {noformat}
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
