0

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

1
  • I'd say the smart move would be to optimize your query so that it doesn't take five minutes to complete. If you decide to follow that route, consider reading this advice.
    – mustaccio
    Oct 6 at 0:05
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?

5
  • Hey thanks for the comment, The truncate table will work for me, and what do you mean by data warehouse? Oct 6 at 13:18
  • @mike_gundy123 - Lots of rows coming in; many queries summarize the things like "number of items sold and the revenue from that -- for a specified day or week or whatever".
    – Rick James
    Oct 6 at 15:22
  • Na, that's not the case. My table is only being updated once a day Oct 6 at 15:30
  • Also for the truncate table option I just need to create the table once (with all the indexes chosen out) and I can constantly just truncate and add into it no problem? Oct 6 at 15:44
  • @mike_gundy123 - See also mysql.rjweb.org/doc.php/deletebig#optimal_reload_of_a_table
    – Rick James
    Oct 6 at 16:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.