1. Problem background: The front page displays data in tables and supports exporting data to EXCEL files. Querying the database is sometimes very slow (for example, the time range is large [2 years], and the SQL statement execution time is almost 220s)
Generally, is it necessary to re-query the database for exported data, or can the data obtained from the front end be directly passed to PHPExcel for processing?
Thanks to everyone who answered.
PS: I have tried my best to optimize the SQL statement (index partitioning has been done for multiple tables and 3 million pieces of table data). If the query execution time is about 1.6s per week, it will take several minutes to check the number of years.
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
html tables can be directly exported to xlsx;
If 1 is not used, the acquired data will be cached before the front-end displays the data, and it can be sent back to the back-end when exporting without the need for another database operation;
2 is that the amount of data is within the acceptable range of front-end and back-end communication (for example, if it exceeds 50MB, it is too large), and the consumption of communication is sacrificed for the consumption of the database.
In fact, js has a plug-in that can process excel, but don’t you still get the data in the frontend from the backend?
JS excel plug-ins, Baidu has a bunch of them, such as exceljs
Some mysql management software, such as navicat, can directly export the database to excel. You don’t have to use PHP to export. Although I can achieve everything with the best language in the world, you should also consider that other things can also be achievedPersonally, I don’t support re-retrieving the data to the background. You can directly export the html to excel as shown above. Of course, you can process it in the background, but when you display the data in html, cache the data to the server. If you export excel, Read the data directly from the cache area and then output it to excel
Generally, exporting data to EXCEL is a back-end operation. Here the user will select certain conditions to export the data. If the backend is time-consuming, it can be processed using asynchronous tasks.
Ask the question, I want to know if exporting data means exporting all data? Or can I choose a time range? If you are exporting everything, you can run a scheduled task in the background. Choose a time when the number of customers is small, such as going to the background at night to check all the data, and then just get it when exporting in the previous period. The scheduled task will also check the data regularly every day. Is it the latest