How to display a huge table from Mysql to the site?

Question

PHP

There is a base on 500 mb.
It is necessary to bring it to the site in the most optimal way so that the page loads quickly.
The table must be paginated.
Columns must be filtered.
What solution can be used?

Answer 1

there are js table plugins (for example, jqgrid) that allow you to display large amounts of data

Answer 2

There is no "optimal" way here, it all depends on the specific requirements of a particular project. It is logical that downloading 500MB at once and moving back and forth in the browser is not such an option.
Load pages with these parts on demand, make requests with filtering parameters when filtering. In general, this is the most banal work with the database, which can only be. Sit down and do it, it's not clear what the question actually is.

Answer 3

I wouldn't bother with premature optimization, and would just screw up normal fetches with a minimum of join \ subqueries, and indexes in the right places. Then I would add caching to the page - it is unlikely that the data is updated every minute there. Most likely, this would be more than enough.
PS In the online store with which I worked, the database has grown to a gigabyte + several million records in the product and user tables. Plus, cumbersome requests with 6-7 joins (the work of the first developer) are a common thing. And nothing, it works pretty fast.