PHP & MySQL Course 7 User Account

Course 7 User Account

PHP & MySQL Course 6 Introduction

Course 6 Introduction

PHP & MySQL Course 5 Working with DATA

Course 5 Working with DATA

PHP & MySQL Course 4 Intro to TABLES

Course 4 Intro to TABLES

PHP & MySQL Course 3 Intro to DATABASES

Course 3 Intro to DATABASES

PHP & MySQL Course 2 Installing MySQL

Course 2 Installing MySQL

PHP & MySQL Course 1 Intro to Variables

Course 1 Intro to Variables

MySQL Practices

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.

The main problem is, it is so easy and hidden from the programmer, most of us tend to ignore it. Some things we do can actually prevent the query cache from performing its task.

1 2 3 4 5 6

// query cache does NOT work $r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()"); // query cache works! $today = date("Y-m-d"); $r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");

The reason query cache does not work in the first line is the usage of the CURDATE() function. This applies to all non-deterministic functions like NOW() and RAND() etc... Since the return result of the function can change, MySQL decides to disable query caching for that query. All we needed to do is to add an extra line of PHP before the query to prevent this from happening.

2. EXPLAIN Your SELECT Queries

Using the EXPLAIN keyword can give you insight on what MySQL is doing to execute your query. This can help you spot the bottlenecks and other problems with your query or table structures.

The results of an EXPLAIN query will show you which indexes are being utilized, how the table is being scanned and sorted etc...

Take a SELECT query (preferably a complex one, with joins), and add the keyword EXPLAIN in front of it. You can just use phpmyadmin for this. It will show you the results in a nice table. For example, let's say I forgot to add an index to a column, which I perform joins on:

After adding the index to the group_id field:

Now instead of scanning 7883 rows, it will only scan 9 and 16 rows from the 2 tables. A good rule of thumb is to multiply all numbers under the "rows" column, and your query performance will be somewhat proportional to the resulting number.

The Beginner’s Guide - MySQL NULL

in this tutorial, you will learn how to work with MySQL NULL values. In addition, you’ll learn some useful functions to deal with the NULL values effectively.

Introduction to MySQL NULL values

In MySQL, a NULL value means unknown. A NULL value is different from zero or an empty string ”.Generally, you use the NULL value to indicate that the data is missing, unknown, or not applicable.When you create a table, you can specify whether a column accepts NULL values or not by using the NOT NULL constraint.

For example, the following statement creates the leads table:

MySQL

1 2 3 4 5 6 7 8

CREATE TABLE leads (     id INT AUTO_INCREMENT PRIMARY KEY,     first_name VARCHAR(50) NOT NULL,     last_name VARCHAR(50) NOT NULL,     source VARCHAR(255) NOT NULL,     email VARCHAR(100),     phone VARCHAR(25) );

The id is the primary key column therefore, it does not accept any NULL value.

The first_name, last_name, and source columns use the NOT NULL constraint, you cannot insert any NULL values into those columns.

The email and phone columns accept NULL values.

You can use a NULL value in the INSERT statement to specify that the data is missing. For example, the following statement inserts a row into the leads table. Because the phone number is missing, so a NULL value is provided.

MySQL

1 2	INSERT INTO leads(first_name,last_name,source,email,phone) VALUE('John','Doe','Web Search','john.doe@acme.com',NULL);

Because the default value of the email column is NULL, you can omit the email in the INSERTstatement as follows:

MySQL

1 2 3

INSERT INTO leads(first_name,last_name,source,phone) VALUES('Lily','Bush','Cold Calling','(408)-555-1234'), ('David','William','Web Search','(408)-888-6789');

MySQL SET NULL in UPDATE statement

To set the value of a column to NULL, you use the assignment operator (=). For example, to update the phone of David William to NULL, you use the following UPDATE statement:

MySQL

1 2 3 4 5

UPDATE leads SET     phone = NULL WHERE     id = 3;

MySQL ORDER BY with NULL

If you use the ORDER BY clause to sort the result set in the ascending order, MySQL considers NULL values are lower than other values, therefore, it presents the NULL values first.

The following statement sorts the leads by phone number in ascending order.

MySQL

1 2 3 4 5

SELECT     * FROM     leads ORDER BY phone;

In case you use the ORDER BY DESC, the NULL values appear at last of the result set. See the following example:

MySQL

1 2 3 4 5

SELECT     * FROM     leads ORDER BY phone DESC;

To test for NULL in a query, you use the IS NULL or IS NOT NULL operator in the WHERE clause.

For example, to get the leads who have not yet provided the phone number, you use the IS NULL operator as follows:

MySQL

1 2 3 4 5 6

SELECT     * FROM     leads WHERE     phone IS NULL;

You can use the NOT NULL operator to get all leads who provided the email addresses.

MySQL

1 2 3 4 5 6

SELECT     * FROM     leads WHERE     email IS NOT NULL;

Even though the NULL is not equal to NULL, two NULL values are equal in the GROUP BY clause.

MySQL

1 2 3 4 5

SELECT     id, first_name, last_name, email, phone FROM     leads GROUP BY email;

The query returns only two rows because the rows whose email column is NULL are grouped into one.

MySQL NULL and UNIQUE index

When you use a unique constraint or a UNIQUE index on a column, you can insert multiple NULL values into that column. It is perfectly fine because in this case MySQL considers NULL values are distinct.

Let’s verify this point by creating a UNIQUE index for the phone column.

MySQL

1	CREATE UNIQUE INDEX idx_phone ON leads(phone);

Notice that if you use the BDB storage engine, MySQL considers the NULL value are equal therefore you cannot insert multiple NULL values into a column that has a unique constraint.

MySQL NULL functions

MySQL provides several useful function that handle null values nicely: IFNULL, COALESCE, andNULLIF.

The IFNULL function accepts two parameters. The IFNULL function returns the first argument if it is not NULL, otherwise, it returns the second argument.

For example, the following statement displays the phone number if it is not known otherwise display N/A instead of NULL.

MySQL

1 2 3 4

SELECT     id, first_name, last_name, IFNULL(phone, 'N/A') phone FROM     leads;

The COALESCE function accepts a list of arguments and returns the first non-NULL argument. For example, you can use the COALESCE function to display the contact information of a lead based on the priority of the information in the following order: phone, email, and N/A.

MySQL

1 2 3 4 5 6 7

SELECT     id,     first_name,     last_name,     COALESCE(phone, email, 'N/A') contact FROM     leads;

The NULLIF function accepts two arguments. If the two arguments are equal, the NULLIF function returns NULL. Otherwise, it returns the first argument.

The NULLIF function is useful when both NULL and empty string values in a column. For example, by mistake, you insert a following row into the leads table:

MySQL

1 2	INSERT INTO leads(first_name,last_name,source,email,phone) VALUE('Thierry','Henry','Web Search','thierry.henry@example.com','');

The phone is an empty string instead of a NULL value.

If you want to get the contact information of leads, you end up with an empty phone instead of the email as the following query

MySQL

1 2 3 4 5 6 7

SELECT     id,     first_name,     last_name,     COALESCE(phone, email, 'N/A') contact FROM     leads;

To fix this, you use the NULLIF function to compare the phone with the empty string, if they are equal, it returns NULL, otherwise it returns the phone number.

MySQL

1 2 3 4 5 6 7

SELECT     id,     first_name,     last_name,     COALESCE(NULLIF(phone, ''), email, 'N/A') contact FROM     leads;

In this tutorial, you have learned how to work with MySQL NULL values and how to use some handy functions to handle NULL values in the queries.