In a relational database with high level of normalization the data is stored in multiple tables. To retrieve the data from many tables joins can be used to connect data from multiple tables or itself(self join). Joins nowadays are considered to be a slow operation, and storage/memory is so cheap that de-normalization is one option in mostly NoSql and Graph databases. Joins can be slow as the database engine has to connect each record from one table to another record in another table with the columns we specify in each.

Trigger is a database object, associated with a table. Trigger does it’s specified function if an event occures such as insert/update/delete records. Benefits of Triggers in MySQL: triggers can change values before insert/update . If you set the trigger to listen on insert/update and the criteria is not met than value will be modifiade this will be applied with the insert. triggers can check values on row level insert or update and it’s able to determine what values where deleted or updated to.

On the dev.mysql.com site you can find few example databases, but for now I will use the world database (innodb). There is a really cool large data dump available from Stack Exchange for performance testing. MySQL world database install guide can be found here, but you can also follow this: Download the world_innodb.sql.zip file Start mysql command line and create the world database Extract the world_innodb.sql.zip to the drive root where mysql is running from on Windows c:/ Go back to mysql command prompt and use the world database created in 2.

I was lucky and I had the chance to study MySQL (&PHP basics) on a 20 week full-time course, with a Tutor who had 15+years experience in web development. He really guided us if something was not clear. Unfortunately nowadays most people just start at home google the subject and start to pick it up. I do/did the same with couple of IT subject but the problem comes if you hurry to have something in your hand to work with and then you fly over things would be a MUST.

Stored procedures can be used to insert new values for more than one table. This could be handy if the data is stored in few tables as well as restricting the inputs coming from the application layer. The following example will show how to create a stored procedure for insert values in MySQL database. This can give the general idea of how to create a stored procedure which contains even some calculation made ‘on the fly’.

Did you ever notice that MySQL auto increment used as default value to write in, if column value is null (or not specified). This is a really great feature, used I think in 80% of tables as id. If you delete the last row of a table eg. where id=5, time comes for inserting a new record with ‘null’ as id value, auto_increment gives id=6 as next to use. In other words if we delete a row auto increment can’t pick up the deleted values.

The name of this database object, view in MySQL can give straight away an answer of the functionality. A view or “virtual table” can be created to retrieve data from tables actually hold the data or from other views. Update, delete, insert statements can be made on the base tables trough a view which can be really useful. Views can be used to define a complicated query to give a specific set of data from tables which gives a good shortcut for later work.

Stored procedures in MySQL can encapsulate statements which can be used later. I think the real power of using stored procedures is in the amount of time can be saved by using one line against maybe dozens of insert, delete etc. Benefits of Stored Procedures in MySQL: Stored Routines can be created with error handlers. Code packaging and encapsulation Separation of the logic Reduction of network bandwidth Security by giving access to a specific routine handling the data.