Sql with a table with 100000 records slow query

Hi if i have a table with 100000 records i want to show in a table just 50 and have pagination. Im using

Select * from table

But takes too long to load query each time. And that table will have option to edit data live. And will have 10 users working on that during the day.

What you recommend to speed up? I cant afford to load 100k records every time someone open that table

Can you use pagination? Documentation: Optimize queries with server-side pagination | Retool Docs

1 Like

Yes will be like this?

select id, first_name, last_name, email from customer where first_name ilike {{ '%' + textinput1.value + '%' }}
order by id
limit {{ table1.pagination.pageSize }}
offset {{ table1.pagination.offset }}

That where is mandatory?

im getting this error

with this query

select * from dbo.tbl_Clientes where dbo.tbl_Clientes.Cedula ilike {{ '%' + textInput1.value + '%' }}
order by dbo.tbl_Clientes.FechaDeSeguimiento
limit {{ table1.pagination.pageSize }}
offset {{ table1.pagination.offset }}

any tips? @PeteTheHeat @Kabirdas
thank you

Hi @agaitan026 Can you try like instead of ilike?

1 Like

same not sure what im doing wrong, also where i got total row count?

convert the {{table1.pagination.pageSize}} to an integer by doing:


that will be for total row count? and what about my current query

Hi there,

It looks like MSSQL uses a different syntax instead of limit offset. Does the solution in this post work?

And then retool docs why said Sql in that part? So thats for postgresql maybe?

what i did is moving out to supabase postgresql but now i got this

  • error:true
  • message:"relation "tbl_clientes" does not exist"
  • position:15
  • :arrow_forward:

queryExecutionMetadata:{} 5 keys

  • estimatedResponseSizeBytes:81
  • resourceTimeTakenMs:181
  • isPreview:false
  • resourceType:"postgresql"
  • lastReceivedFromResourceAt:1697829938337

but the table and column does exists


also the column named Cedula, what im doing wrong?

the query on retool is:

select * from tbl_Clientes where tbl_Clientes.Cedula ilike {{ '%' + textInput1.value + '%' }}
order by tbl_Clientes.FechaDeSeguimiento
limit {{ table1.pagination.pageSize }}
offset {{ table1.pagination.offset }}

fixed by using this quotes

but now pagination is not working, i cant see the other results, only one page

It looks like your total row count is equal to the page size - total row count should evaluate to the number of all rows (100000)

And yes, you're correct - our docs typically use Postgres as an example!

1 Like

Ok so that should be a new query to count rows and use that var

Yes, you can have a separate query that fetches the total record count

1 Like

but my issue now is when i use filters i can only search the paginated page , it wont look up all dataset (thats what i need when filters are used)

What you mean page size - rowcount?

What this means ?

Be sure any filters used in your pagination query are also used in the total row count query, so each returns the same number of rows.

Hi there,

row count is the total number of records when the data is not paginated.

1 Like