Create new sql table based on a query from another table MariaDb

Question

Just to preface this I am very much a noob when it comes to dbs.

As the title states I want to create a new table based on query I do on an already existing table. At first I thought a view would be a good idea since I am not creating new data but removing redundant rows. The issue is the query takes around ~5min to do and that's way too slow, so I figured instead of creating a view why not use the query to create a new table that is populated once a day.

Would the smart move be to make a Scheduled Event that runs the query a day and generate the table that way?

I am using Mariadb with HeidiSQL as the IDE

Thank you in advance for all help

Answer 1

• VIEWs are syntactic sugar, not a way to speed up anything.

• This will create a new table from a select:

    CREATE TABLE foo
        SELECT ....;
  

• This creates a TEMPORARY table that will go away when you disconnect:

    CREATE TEMPORARY TABLE foo
        SELECT ....;
  

• To rebuild the table daily, this will repopulate it:

    TRUNCATE TABLE foo;
    INSERT INTO foo
        SELECT ....;
  

• The Truncate technique leaves the table empty for 5 minutes. If that is not acceptable, there is a workaround.

• If the source table is a "data warehouse", then building and maintaining a "summary table" will cut the "5 minutes" down significantly.

Which of those would you like to discuss further?