Queri slow for count result found

App Building
1

hi guys i got stuck on this, can someone give me some solution?
so the scenario issue like this
i have rows data more than 5 millions ,
so i make query for (count result found) example
i make 3 different search :

1.search byname
example data name
(Laptop asus tuf gaming adm ryzen 5)
because data long, so i use "ILIKE" for search

  • 2.filter by shop name
    example data shop name (tufStore)

  • 3.filter by country
    example data country (maroko)

the issue is query for count result found so slowly (>20s)
so this query will run if user click button search / enter on keyboard

for the database part already indexing but still slowly for query count result found

so the main issue is query for count result found so slowly (>20s)

and I took the initiative to compare several queries (5 queri) for count result found

in this comparison i use the same keyword

    1. QUERY 1 (24.4s)
      this the query :
SELECT COUNT(p.id) as total_rows
FROM
    products p
LEFT JOIN
    stores s ON tp.store_id = s.id
LEFT JOIN
    countries c ON p.country_id = c.id
WHERE
(
  c.name ILIKE '%' || COALESCE({{FilterByCountryy.selectedLabel}}, '') || '%'
)
AND (
    p.name ILIKE '%' || COALESCE({{InputName.value}}, '') || '%'
)
AND (
  s.shop_name ILIKE '%' || COALESCE({{FilterByShop.selectedLabel}}, '') || '%'
)
    1. **QUERI 2 ** (24.7s)
      this the query :
SELECT COUNT(p.id) as total_rows
FROM
    products p
WHERE
(
    p.name ILIKE '%' || COALESCE({{InputName.value}}, '') || '%'
)
AND
p.country_id = {{FilterByCountryy.value}}
    1. QUERI 3 (25.3s)
      this the queri:

SELECT COUNT(p.id) as total_rows
FROM
    products p
WHERE
  p.name ILIKE '%' || COALESCE({{InputName.value}}, '') || '%'
  • 4 . QUERI 4 (22s)
    this the query :
SELECT COUNT(p.id) as total_rows
FROM
    products p
WHERE
(
    p.name ILIKE '%' || COALESCE({{InputName.value}}, '') || '%'
)
AND
p.store_id = {{FilterByShop.value}}
    1. QUERI 5 (19.8s)

this the queri :

SELECT COUNT(p.id) as total_rows
FROM products p
WHERE 
(
    p.name ILIKE '%' || COALESCE({{InputName.value}}, '') || '%'
)
    AND p.store_id = {{FilterByShop.value}}
    AND p.country_id = {{FilterByCountryy.value}};
1 Like
2

i recommend you using server side pagination Optimize queries with server-side pagination | Retool Docs.

3

i already use it, still slow for count result found

1 Like
4

@Nico_Revaldo The reason the query is slow is because the WHERE statement is based on a ILIKE filter. The database can't optimize it with any index. It'd have to go through all 5 million rows to check whether it passes the filter or not.

I recommend using some additional filter to reduce the search size (e.g. filter by tiktok_store, or by country, ...). If the value of name/label are predefined strings, you can index them and search for exact value as well.

2 Likes
5

correct but also pagination may help, and i cant see that in his code, if i see that query its looking at the whole dataset @Nico_Revaldo

6

for example i have also an app with some custom filters i got almost 3M records and its very fast with pagination and that filters {{ ! cedula.value }} so if user doesnt put anything it will go to all dataset using pagination of course

WHERE 
  (
    {{ ! cedula.value }} 
    OR c.cedula ILIKE {{ '%' + cedula.value + '%' }}
  ) 
  AND (
    {{ ! sector.value }} 
    OR c.sector = {{ sector.value }}
  ) 
  AND (
    {{ ! analista.value }} 
    OR c.analista ILIKE {{ '%' + analista.value + '%' }}
  ) 
  AND (
    {{ ! ubicacion.value }} 
    OR c.ubicacion = {{ ubicacion.value }}
  ) 
  AND (
    {{ ! motivo.value }} 
    OR o.motivo = {{ motivo.value }}
  ) 
  AND (
    {{ !(rangodefecha.value.start) }} 
    OR (
      o.fechaproxima_llamada <= {{moment(rangodefecha.value.end).format('YYYY-MM-DD') }} 
      AND o.fechaproxima_llamada >= {{moment(rangodefecha.value.start).format('YYYY-MM-DD') }}
    )
  ) 
ORDER BY 
  c.apellidos, 
  o.fechaproxima_llamada DESC NULLS LAST, 
  idclave_nombre 
LIMIT  {{table1.pagination.pageSize}} 
OFFSET {{table1.pagination.offset}}
7

I've done that for that but it's still slow,

and why I use ilike is because the product name is long
like this :

Handheld Toilet Bidet Faucet Sprayer Stainless Steel Bathroom Hand Bidet Spraye Gun Set Toilet Self Cleaning Shower

1 Like
8

btw i make different queri for search data and for count result

this is for search data (2s)

SELECT
    p.*,
    s.business_name AS shop_name,
    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
(
  c.name ILIKE '%' || COALESCE({{FilterByCountryy.selectedLabel}}, '') || '%'
)
AND (
  p.name ILIKE '%' || COALESCE({{InputName.value}}, '') || '%'
)
AND (
  s.business_name ILIKE '%' || COALESCE({{FilterByShop.selectedLabel}}, '') || '%'
)
LIMIT {{t1.pagination.pageSize}}
OFFSET {{t1.pagination.offset}}

and for the queri for count result is above that (comparison of 5 queries)

1 Like
9

I think you'll have to forgo the count query then... You could do something like showing 1000+ results instead of trying to provide the exact numbers, or do another query to see whether there is a next page.

1 Like
10

In terms of DB model design, maybe instead of letting people enter free-style keyword, you can do it based on pre-generated tags.

E.g.

(Handheld Toilet Bidet Faucet Sprayer Stainless Steel Bathroom Hand Bidet Spraye Gun Set Toilet Self Cleaning Shower)

You can generate the tags as handleld, toilet, ... and by indexing those, it'll be faster to do filter.

1 Like
11

So to find out if there is a next page, still use count right

12

So to find out if there is a next page, still use count right

Instead of using the count, you can fetch the next page to see if there is any row left

13

@Nico_Revaldo Hi Nico, just checking in to see if you were able to resolve this following Harry's suggestion. Let me know if you have any questions.