I want to ask a question that involves several topics: a PHP framework, MySQL database, user experience, page loading performance, server performance. Since the question is complex, I can't find a suitable answer after thoroughly googling.
I feel that if I reduce the scope of the question I may be having an incomplete answer. But maybe that is the only way to tackle this.
I'm not sure which Stack Exchange site would be the best fit for that question. For example, Stack Overflow would seem a good fit, but it would lack the user experience part, or it would be deemed as off-topic. The same could happen with other Stack Exchange sites.
The actual question would be:
What should be the best practice when fetching records from the database to show in an index page?
I'm developing an application using the framework CakePHP, with MySQL as database.
The maximum number of records to fetch from a table are not more than 2000.
To show in an index page, I'm fetching the whole 2000.
The user base of the application is no more than 10.
I found that the delay fetching the 2000 +/- records is negligible in page loading. And as all the records are in the client's side, making searches via DataTables without any delay.
Modern web browsers and internet speeds seems to handle this amount of data without a blink.
Discussing with some colleagues, they told me that I should paginate fetching, 10 (for example) records at a time, making a query to the server each 10 rows.
My take on this is that fetching 10 records at a time (I'm not using Ajax), could hurt the user experience, since implies a delay in every click and in every search. Also seems to me that the server side performance and efficiency is not really something serious.
Taking into account all of these considerations,
is there a preferable maximum of records to be fetched, or the best practice is always fetching n records at a time?
Do you have a site recommendation, or how should I approach this situation?