I recently tuned an existing application that used dozens of views and hundreds of stored procedures using MySQL 5.6.
There seems to be three attitudes towards using views and stored procedures (SPs) with MySQL:
- don’t use them at all to increase portability
- just use SPs to reduce network traffic in large reporting queries (my choice)
- go crazy and use them everywhere like old-school Oracle Enterprise apps.
Here are some notes on using views:
- before creating views, review your schema to ensure keys have matching types and charsets for good performance. It’s much easier to spot schema problems in a text listing than to guess why a view is slower than expected at execution time. (This is doubly true for MySQL Cluster.)
- MySQL currently doesn’t have CREATE VIEW FORCE, although MariaDB will later in 2014. The FORCE option will greatly simply view administration and also mysqldump output, which creates temporary tables to ensure views can be created regardless of table/view ordering issues
- When looking at the MariaDB source code, it’s apparent that some view options were never actually implemented, like RESTRICT/CASCADE
And some notes on stored procedures (SPs):
- if a SP makes a stateful session change, like set sql_log_bin=0, ensure that isn’t going to be a problem later if an exception condition doesn’t reset it
- after running a SP, SHOW PROFILES will list all the queries executed with performance statistics
- SPs that do non-essential SELECTs or INFORMATION SCHEMA queries probably need to be reviewed by a DBA for fundamental problems like non-atomic “reading before writing”
Both views and SPs are relatively new MySQL features, so budget some extra development and testing time when using them, especially with replication.
[MDEV-6365] CREATE VIEW Ignores RESTRICT/CASCADE Options
mysqlperformanceblog.com: Using MySQL triggers and views in Amazon RDS
