System Information
- Strapi Version: 3.3.4
- Operating System: linux
- Database: postgres
Hi,
Could someone please point me in the right direction when trying to at least locally optimize the search queries being used in the Admin gui? When searching for a collection type strapi executes the following query:
select distinct “someEntity”.*, “someEntity”.“someField1” as “_strapi_tmp_someEntity_internalName” from “someEntity” where (“someEntity”.“someField1”::text ILIKE ? or “someEntity”.“someField2”::text ILIKE ? or “someEntity”.“someField3”::text ILIKE ? or “someEntity”.“someField4”::text ILIKE ? or “someEntity”.“someField5”::text ILIKE ? or “someEntity”.“someField6”::text ILIKE ? or “someEntity”.“someField7”::text ILIKE ? or “someEntity”.“someField8”::text ILIKE ? or “someEntity”.“someField9”::text ILIKE ? or “someEntity”.“someField10”::text ILIKE ? or “someEntity”.“someField11”::text ILIKE ? or “someEntity”.“someField12”::text ILIKE ? or “someEntity”.“someField13”::text ILIKE ? or “someEntity”.“someField14”::text ILIKE ? or “someEntity”.“someField15”::text ILIKE ? or “someEntity”.“someField16”::text ILIKE ? or “someEntity”.“someField17”::text ILIKE ?) order by “_strapi_tmp_someEntity_someField1” asc limit ?
And when searching for an image to link to a collection type this one is executed:
select distinct “upload_file”.*, “upload_file”.“updated_at” as “_strapi_tmp_upload_file_updated_at” from “upload_file” where (“upload_file”.“name”::text ILIKE ? or “upload_file”.“alternativeText”::text ILIKE ? or “upload_file”.“caption”::text ILIKE ? or “upload_file”.“hash”::text ILIKE ? or “upload_file”.“ext”::text ILIKE ? or “upload_file”.“mime”::text ILIKE ? or “upload_file”.“url”::text ILIKE ? or “upload_file”.“previewUrl”::text ILIKE ? or “upload_file”.“provider”::text ILIKE ? or “upload_file”.“id”::text ILIKE ?) order by “_strapi_tmp_upload_file_updated_at” desc limit ?
As a long term solution I’d suggest adding a property on collection type giving users the option to set if said field should be included in the above search (for instance upload_file.hash and upload_file.previewUrl would be two candidates to remove) but as a more short term I’d like to try to just limit the fields as our database server is going nuts when 20-ish users are searching at the same time. I’ve tried finding where the actual query is being built but react isn’t really my got tech so I haven’t been able to do so.