Articles in MySQL Category

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.


A subquery is a SELECT statement placed inside another statement. Given that, you can write a subquery in place of the “from table” in a SELECT statement or in a WHERE clause in place of the values.

You would find that most statements with subqueries can be written as JOIN or UNION statements. But you would also notice that subqueries are more readable compared to complex joins.


Consider views as derived tables from existing tables. Views can be useful for summary tables with aggregated data and table joins.

Once defined, you can execute SELECT statements on views as you do on tables. INSERT, UPDATE and DELETE statements are also possible provided that the views don’t contain aggregated data.

User Variables

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.

Prepared Statements

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.

Scheduled Events

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.

Where to Head from Here...
Share with Your Peers...

We Value Your Feedback...

We love to hear what you think about this article. Please provide your opinion, suggestions and improvements using following form. Note that submitted feedback is not displayed but we will get back to you if it needs a reply.