I have a 4-core MySQL server with 4 GB of RAM, holding 19 MyISAM tables (total: ~164GB of data) and 32 innodb tables ( 5mb ) and we use it to process CDR files( basically call detail records files)
I have a table(billing_datas) with about 200 million records.( myisam table). ( basically we store all the cdr files data with the ids of carriers etc) which keeps on growing at a rate of 90 million rows per month
We need to generate reports based on selected "date from" and "date to" criteria. I m confused with the approach
The current scenario that we have is we are firing the query on the main table i.e billing_datas and creating the temporary table with "date from" and "date to" parameters and performing the aggregate calculations on this temp table. But the real performance hit is creating the temporary table is taking about 18 seconds for 1 month records.
should we create 3 summary tables based on summary_monthly,summary_weekly,summary_daily and run the reports query on these summary tables??
will it improve the performance as described in point 1?