MySQL create tables using a cron job or use Views

Question

I have a number of complex queries, the results of which were being stored in MySQL views. The problem is that MySQL views suffer in performance terms.

I set up a cron job to populate a standard table with the same data that the views were populated with:

DROP TABLE user_reports;
CREATE TABLE user_reports
  SELECT col1, col2, col3 FROM 
  /** COMPLEX QUERY **/
;

Now, when making queries on the cron-populated user_reports table, queries take almost a tenth of the time to query compared with the equivalent view.

Is this a common approach? Obviously there is some burden on the server every time the CRON job is run and it means that data is not available live.

Time taken to query user_reports = 0.002 seconds
Time taken to query view_user_reports = 0.018 seconds

This all said, maybe a query that takes 0.018 seconds to run should be run from application code, rather than stored in views? Although I don't think it would scale as well as the cron-driven method.

Answer 1

the results of which were being stored in MySQL views

Oh dear. MySQL views DO NOT store data.

DROP TABLE user_reports;

CREATE TABLE user_reports

...something wrong with....

TRUNCATE TABLE user_reports;

?

I don't think it would scale as well as the cron-driven method

Only as long as the query in the cron job doesn't take long to run - when it does you'll need to start thinking about incrementally adding data to the pre-processed result set. But at 0.018 secs, this is just premature optimization.