Advanced MySQL Features
In our article series on MySQL, we have covered most of the MySQL features that you would encounter in PHP applications. However MySQL is a sophisticated database management system that offers advanced features.
Following is a brief on some advanced features MySQL offers. If you wish to learn more on these features, follow relevant online resources or consider reading a textbook.
When you execute a SELECT statement that involves the primary key column, MySQL can quickly determine the position to search for because primary key is an index. Similarly indexes can be defined for other columns in a table to speed up querying.
Defining indexes should be done with care because wrong indexes can lead to slowing down queries instead of speeding up.
Consider views as derived tables from existing tables. Views can be useful for summary tables with aggregated data and table joins.
You can define variables for current database connection and use them in the statements being executed. Once the connection is closed, the variables are lost. User variables are mainly used in prepared statements and stored procedures.
MySQL allows you to store SQL statements in current session and reuse them with user variables which are called as prepared statements. They are useful when a set of statements only differ slightly from each other.
For an example, you can write a prepared statement for a set of SELECT statements where you only need to change the values used in WHERE clause. Prepared Statements are efficient compared to the corresponding set of raw SQL statements since the traffic between the client and the server is low.
Stored Procedures and Functions
Stored procedures are SQL programs written and stored in MySQL server (Unlike prepared statements, stored procedures are not limited to current user session). Once defined, you can call a procedure in command-line or in a PHP script by its name. You can also edit and delete a procedure.
A stored procedure can contain a simple SELECT statement or logical statements with variables, conditional statements and loops. Stored procedures are efficient since they are compiled before storing and the traffic between client and server is reduced (You just have to send the name). However they increase the load on the server.
Stored functions are similar to procedures while main differences are the return values and the ways they are invoked. A procedure can return a result set as normal SELECT statements and should be called directly (via a CALL statement). A function returns a single value as built-in MySQL functions. They can not be called directly and should be included in SQL statements like SELECT.
Triggers can be defined for a table to be executed before or after an INSERT, UPDATE or DELETE statement is executed. Triggers can be used as data filters or to log data changes taking place in a table.
For an example, a trigger can be defined before an INSERT statement to make sure values for a certain column fall between a given range, and/or after a DELETE statement to log the deleted data in another table.
MySQL scheduled events let you run SQL statements at given intervals. For an example, if you have a column where the values should be incremented daily, you can write necessary SQL statements and schedule them to be executed daily at a set time.