Report generation for millions of rows in mysql

Question

We create a site and for which we read the txt file (using PHP-mysql) from the server and save it in the mysql database (using load in file query). Its temporary database, and then this data is transferred to another database with some additional information, for example, identifiers, codes, etc. Then a new database is created and will be reset with another txt file.

a database with additional information is growing faster.

Text files continue and the database grows as 2 million records per day. about an average of about 15-20 files arrive at any time interval. It has no specific time that the files do not arrive.

we carry out a certain group upon request and reduce the data in the database with additional information to about 47 thousand out of 7 million records and save them in another database for generating reports. This data (47K) is calculated for 4 days (report database) and for the purpose of the report. therefore, at the end of the month, the main database with identifiers, codes can grow up to 70 million records.

we must create reports simultaneously from the table daily and weekly, as well as monthly.

as the database grows at a faster rate, how to process reports after 1 month, for example, if they want the report from jan to April, for example ..

how can we consider this scenario, since reading / writing are performed in the same table, which is growing at a tremendous speed.

Answer 1

Not knowing what is happening with the data, but it doesn’t really matter to me what you read from the txt file into the database ... massage it and add other link codes, then resubmit it to the txt file to save it in the database . I would leave this in the database.

At what speed (disk capacity) are recordings added?

What types of reports are generated ... Statistical? General aggregation?

If it were just general aggregations, I would save a separate table in the database with any categories of collapsed numbers, classifications, etc. Then it would be possible to run reports from this, instead of re-importing all those text files that you uploaded. Having a “pivot” table, both on an hourly basis (or even on a daily basis), can easily simplify the reporting process.

Without seeing some sample of data and expected reporting results, it would be difficult to consolidate any one approach.

---- According to comments comments

When dealing with call data record (CDR) information in the past, there is also a lot of garbage. The overall report of what I consider critical will be very limited. In addition, the knowledge of telephone companies has only a few million calls per day, and tracking them can overload your database capacity, so the archiving process will make sense, but there are not so many general summaries. Even if you tracked all outgoing numbers and how many times you called the recipient’s number ... or how many common destination numbers coming from different numbers of origin, you could collapse and limit the general statistics of the reports.

--- Example: If gov't tracks calls (no ... do they?) To people, they may want to find out how many different people call person X and how many people X also call. Of concern in BOTH areas of communication. As soon as the template is identified, and throwing alarms, they can focus and save entries in a separate tracking table, which is NOT reset after creating text output files ... And no, I do not work for the government or any such agencies who can do this.

- What you need to keep. I can only offer suggestions about what type of information you are trying to save ... you are not indifferent to the PBX system, are there trunk lines, extensions? I would start by first storing the critical elements that you want, and strip out what might be useless information from each record storage database.

Origination Calling Number/Extension Destination Number International Country being called Call Time Start Call Duration 

More than 10 years have passed since I worked with PBX and Call-Data-Records systems, but I know that this should be the common core for the data. Depending on how the CDR format looks, you may have to standardize the called number. if one system stores as one full phone number and the other is kept separately for the internal call prefix, you will not find dialing matches

555-1234 vs +014 555-1234

I know that a fake phone number is not legal, but I'm sure you understand where I come from.