Hi friends, I am facing a difficult situation and need help. Can someone provide a solution?
The problem scenario is as follows: I have more than 5 million rows of data and am creating a query to determine the availability of a service or vendor. An example of the expected output is:
“vendors with keyword “writing” found in “3” services and available in “19” vendors.”
However, there are problems with performance, where requesting calculation results takes a very long time (>30 seconds). The query will only be executed when the user clicks the search button or presses the enter key on the keyboard.
Indexing has been carried out in the database section, but the results are still slow. The main problem was the time required to generate the calculations, which turned out to be very slow (>30 seconds).
and this my sql code
SELECT DISTINCT
s.business_name AS shop_name,
p.service AS product_service,
p.vendor AS product_vendor,
c.name AS country_name
FROM
products p
LEFT JOIN
stores s ON p.store_id = s.id
LEFT JOIN
countries c ON p.country_id = c.id
WHERE
(
{{ ! FilterByCountry.selectedLabel }}
OR c.name ILIKE {{ '%' + FilterByCountry.selectedLabel + '%' }}
)
AND
(
{{ ! InputVendor.value }}
OR p.vendor ILIKE {{ '%' + InputVendor.value + '%' }}
)
AND
(
{{ ! FilterByService.selectedLabel }}
OR p.service ILIKE {{ '%' + FilterByService.selectedLabel + '%' }}
);
For example, we have Nico Vendor data available in several countries and various Services. Let's focus on just two countries: Vietnam and Afghanistan, and some Vendors in each country.
- Country:
-
Vietnam
-
Afghanistan
- Vendors:
- Vietnam:
(Vietnam Vendor , Vendor World Vietnam)
- Afghanistan:
(ABC Tech)
Desired output:
When users search for Vietnamese Vendors, the country and store filters will provide relevant options:
- Country:
- Vietnam
- Vendors (based on selected country):
-
Vietnamese Vendor
-
Vendor World Vietnam
By limiting the display of countries and stores to only those that are relevant, users will not see countries or stores that do not have data related to Vietnam Vendor products.
so i can make some information label like this
“vendors with keyword “Vietname” found in “3” services and available in “19” vendors.”