[SOLVED] MERGE engine vs { joins | views }

[lampstax]

Sorry if this has been asked before, but searching for 'MERGE' on this forum returns too many irrelevant results.

 

Can someone tell me when you would use the MERGE storage engine vs joins or a view ? 

 

I read the MySQL book, and the example they gave, is if you had a separate table for countries each continent, you can create a MERGE table and get all the countries for all continents.

 

From what I know, I can achieve this result by creating a view, or a complex join.

 

So what would be the benefit to using MERGE vs joins and/or views ?

 

 

[fenway]

MERGE just makes it easier to not have to update code to deal with "moving" records... for example, you can have a MERGE table called "record_archive", which contains all "old" rows  from "record" table after a certain date.  But, using MERGE, that record_archive table could actually be 10 underlying tables, one for each of the past 10 years.  When you add a new year, you don't have to do anything to the codebase -- you just change the MERGE table definition.  Make sense?

[lampstax]

Yeah.  That makes sense.  If it was a join or view .. I would have to add the new table into the join .. simple enough .. but it could make a huge although simple sql statement .. and i would have to make that change everywhere .. with Merge, i can just modify the merge table definition once a year!

 

thanks!

[fenway]

Yes, just modify the MERGE definition whenever you'd like.