Mapping series – an aside, a note on views in MySQL

While working on a project recently – a mapping one which required complex queries – I worked more with MySQL views. They undeniably make looking at complex data sources easier – you can see the aggregated data while masking the complexity of queries behind them. However they have 2 significant downsides:

  1. They do not actually make the queries any faster. Without anything additional, they are just a presentational aid that makes things appear simpler.
  2. They store the username and password that initialises them; this means that when migrating the database – from staging to live for example – if the same credentials don’t exist on both locations you will get errors. Nothing insurmountable, but care must be taken.

That’s not to say there’s anything wrong with views. However this experience gave me the idea that their place, in the work I do, should be very limited. When you are writing both the database and code to call it, a well written interface provides all the benefits of abstraction with none of the risk.



Leave a Reply

Your email address will not be published. Required fields are marked *