Json big data return

If you suggest it is paging, then tell me how can I do it through the UI and serverside implementation.

in your ajax call, you use an endpoint which MUST handle :
- the number of records to send back
- the offset to say from which row you need to send the number of recors requested

so instead having a BIG result from : select * from products;
you have select column1, column2, columnx from products offset 30 rows fetch next 10 rows only
where 10 is the number of result requested
and 30  ask to send the third page (3 x 10)

currently :
$.ajax("/path/to/endpoint",

handing quantity and page :
now :
$.ajax("/path/to/endpoint?quantiy=10&page=3",

simple UI :
<select name="page">
   <option>1</option>
   <option>2</option>
   <option>3</option>
...
   <option>X</option>
</select>

you can create this with javascript once you get the total number of row : nb_total_row / nb_per_page

Thank you for your answer.

I am doing what you described right now, but the issue is when I have 5000000 rows and I return just the first...let's say 5000 rows and now user go and do sorting, then it will sort just for 5000 rows.

Also, when you do the paging on 5000000 rows it comes very slow even though I request just 5000 at a time.

you should not request 5000, only what you need to display
create and send 5000 rows of data is too big for your server, bandwidth and client browser and maybe useless until you need to create graphics for a period and not global stats
so request 100,200, just what a basic HTML is able to show in one shot on a 8K (!!!) screen monitor

k, good point.

Now I return 500 rows per page, so still how am I going to solve the sorting issue.?
Right now I send ajax to the server and get in return 500 rows in the first page,, but the user wants to have all the 5000000 rows as he want to sort etc.

the way you did it to only return 500, do the same work to let it return 100

I got this point. I just don't get how can I sort between 5000000 rows when I return 500 or 100.

why do you need to "sort" them ?
your SQL query MUST limit this, it's not about client or server code, just update your current SQL query to  handle this (LIMIT and OFFSET)

So, you say just send a variable back to the server to sort the data and return back to UI?

k, I got your idea.

Thank you for help.