Extracting reports from big tables in chunks

user_5733652 0
Viewed 141

The question might sound a bit general. Still. We have a table with hundreds of millions of records. To make the report, several other smaller tables are being joined with it. Indexes are created for all appropriate columns. The client wants to get a report for a year+, that might be up to 100mil rows.

In order to secure the process, say if the script dies, or if the connection to the DB drops, the report must be extracted in chunks, so the next process picks up the report where the previous died.

The problem is that the report can be sorted by varchar/int columns, which can contain client names, account numbers, various personal data in different formats etc etc, and i haven't sorted out how to get a reasonable amount of rows for each chunk (say ~50k) in these cases.

Using limit x,y will take way too long with this amount of data. There are no archived tables, no partitioning, data is not aggregated to separate tables. Just a huge chunk of data in one table.

Is there an established (magic?) way to deal with this kind of problem?

bigdata mysql php
1 Answers

"Is there an established (magic?) way to deal with this kind of problem?" -- You are asking about several things that I have spent a lot of time perfecting.

"hundreds of millions of records ... report" and "the report can be sorted by various columns" -- With a Summary table(s), that can run a lot faster: http://mysql.rjweb.org/doc.php/summarytables . And a summary table helps with the sorting.

"Indexes are created for all appropriate columns" -- Sorry for my cynicism, but I have found that that phrase means that you have a bunch of single-column INDEXes, but have not learned about the benefit of suitable 'composite' indexes. http://mysql.rjweb.org/doc.php/index_cookbook_mysql and http://mysql.rjweb.org/doc.php/index1

"using LIMIT and OFFSET" -- Here's why it is very inefficient: http://mysql.rjweb.org/doc.php/pagination . I don't see OFFSET being needed to solve your problem. 'Remembering where you left off' may helping in chunking to initially construct the Summary Table(s).

"connection to the DB drops" -- The above tips should make it fast enough to avoid this issue. If not, please provide SHOW CREATE TABLE, SELECT, and possibly other details.

"chunking" -- Here are tips on chunking, but I don't think that is the solution for the problem at hand: http://mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks

"hundreds of millions of records ... in one table" -- Not a problem. My tips are aimed at that size.

"no partitioning" -- Good. I won't have to spend time convincing you that partitioning is unlikely to help: http://mysql.rjweb.org/doc.php/partitionmaint

You win the prize. I don't think I have ever pointed at 6 of my blogs to answer one Question.

user_17668310
Related