MySQL 5 introduced a plethora of new features – stored procedures being one of the most significant. In this tutorial, we will focus on what they are, and how they can make your life easier.

Introduction

“ A stored routine is a set of SQL statements that can be stored in the server.”

A stored procedure is a method to encapsulate repetitive tasks. They allow for variable declarations, flow control and other useful programming techniques.

The “academic” position on this is quite clear and supports the extensive use of stored procedures. On the other hand, when you consider the opinions of those who work with them day in, day out, you’ll notice that reactions vary from complete, unwavering support to utter hate. Keep these in mind.

In today’s video quick tip, we’ll have a quick (not sponsored) overview of a Mac database manager for MySQL that I came across yesterday, called Querious. As I mention in the screencast, I particularly like this one, because it just works — without being too complicated or overwhelming in the way that PHPMyAdmin might be for some.

Chances are, you know what a database trigger is, at least in conceptual terms. Chances are even greater that you know that MySQL supports triggers and has supported them for quite some time. I would guess, even armed with this knowledge, that a good many of you are not taking advantage of triggers with MySQL. They’re one of those things which should absolutely be in your development toolbox, as they can really change the way that you look at your data.

Many PHP programmers learned how to access databases by using either the mysql or mysqli extensions. Since PHP 5.1, there’s been a better way. PHP Data Objects (PDO) provide methods for prepared statements and working with objects that will make you far more productive!

Today, we continue our journey into the world of SQL and relational database systems. In this part three of the series, we’ll learn how to work with multiple tables that have relationships with each other. First, we will go over some core concepts, and then will begin working with JOIN queries in SQL.

Catch Up

• SQL for Beginners: Part 1
• SQL for Beginners: Part 2

Introduction

When creating a database, common sense dictates that we use separate tables for different types of entities. Some examples are: customers, orders, items, messages etc… But we also need to have relationships between these tables. For instance, customers make orders, and orders contain items. These relationships need to be represented in the database. Also, when fetching data with SQL, we need to use certain types of JOIN queries to get what we need.

It is important for every web developer to be familiar with database interactions. In part two of the series, we will continue exploring the SQL language and apply what we’ve learned on a MySQL database. We will learn about Indexes, Data Types and more complex query structures.

What You Need

Please refer to the “What You Need” section in the first article here: SQL For Beginners (part 1).

If you would like to follow the examples in this article on your own development server, do the following:

1. Open MySQL Console and login.
2. If you haven’t already, create a database named “my_first_db” with a CREATE query.
3. Switch to the database with the USE statement.

Database operations often tend to be the main bottleneck for most web applications today. It’s not only the DBA’s (database administrators) that have to worry about these performance issues. We as programmers need to do our part by structuring tables properly, writing optimized queries and better code. Here are some MySQL optimization techniques for programmers.

1. Optimize Your Queries For the Query Cache

Most MySQL servers have query caching enabled. It’s one of the most effective methods of improving performance, that is quietly handled by the database engine. When the same query is executed multiple times, the result is fetched from the cache, which is quite fast.