This has to be the one of the most in depth presentation I have ever seen about MySQL explain.

If you are working with MySQL, EXPLAIN is crucial whether you are a developer or a DBA.

And recently, I have also been using EXPLAIN quite frequently both in my works and my posts.

Couple of funny pictures attached in my office wall that I want to share.

Bug versus Feature

Photoshop is always true at least about Bush

Indexes are used for reading the data from the table with comparatively faster execution time. When an index is defined, MySQL maintain separate list of index values and update them when the values are updated / changed. Defining index will require more storage that means we are trading off space with the speed.

MULTI-COLUMN AND SINGLE COLUMN INDEX

Lets consider this table and its index:

create table employees (
id int auto_increment,
first_name varchar(32) not null,
last_name varchar(32) not null,
phone_number varchar(32) not null,
primary key(id)
);

ALTER TABLE employees ADD INDEX first_last_name (first_name, last_name);

mysql> select count(*) from employees;
+----------+
| count(*) |
+----------+
|   100001 |
+----------+
1 row in set (0.01 sec)

Index Cards' "DNA" By ayalan

Now lets make selection:

SELECT * FROM employees where last_name='tuladhar' and first_name = 'prajwal';

mysql> explain select * from employees where last_name='tuladhar' and first_name='prajwal' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: employees
type: ref
possible_keys: first_last_name
key: first_last_name
key_len: 196
ref: const,const
rows: 1
Extra: Using where
1 row in set (0.00 sec)

By using EXPLAIN keyword we can know which keys are being used in the query. (Focus on possible_keys and key column)

This is simple example of using multi-column index.

Now, instead of using, double criteria, lets use only single.

SELECT * FROM employees where last_name='tuladhar';

mysql> explain select * from employees where last_name='tuladhar' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: employees
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 100001
Extra: Using where
1 row in set (0.00 sec)

Did you see that the key column is null. So, the query is not taking advantage of any index at all. This is because we have only defined two indexes: a primary key and multi-column index containing first_name and last_name.

Now lets use ORDER BY clause to sort data using columns: first_name and last_name

SELECT * FROM employees order by last_name, first_name LIMIT 10;

mysql> EXPLAIN SELECT * FROM employees order by last_name, first_name LIMIT 10 \G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: employees

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 100001

Extra: Using filesort

1 row in set (0.00 sec)

If the sort order has last_name as the first column while first_name as the last, index won’t be used. In order to make the query use the index we have to :

SELECT * FROM employees order by first_name, last_name LIMIT 10;

mysql> EXPLAIN SELECT * FROM employees order by first_name, last_name LIMIT 10 \G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: employees

type: index

possible_keys: NULL

key: first_last_name

key_len: 196

ref: NULL

rows: 10

Extra:

1 row in set (0.00 sec)

Now lets create two additional single column index:

ALTER TABLE employees ADD INDEX first_name (first_name);

ALTER TABLE employees ADD INDEX last_name (last_name);

SELECT * FROM employees where last_name='tuladhar' and first_name = 'prajwal' ;

mysql> EXPLAIN SELECT * FROM employees where last_name='tuladhar' and first_name= 'prajwal' \G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: employees

type: ref

possible_keys: first_last_name,first_name,last_name

key: first_last_name

key_len: 196

ref: const,const

rows: 1

Extra: Using where

1 row in set (0.00 sec)

In the above query, there are 3 possible queries and MySQL is using the multi-column index. We can force MySQL to use other two indexes by giving it hint.

SELECT * FROM employees IGNORE INDEX(first_last_name) where last_name='tuladhar' and first_name = 'prajwal' ;

mysql> EXPLAIN SELECT * FROM employees IGNORE INDEX(first_last_name) where last_name='tuladhar' and first_name = 'prajwal' \G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: employees

type: ref

possible_keys: first_name,last_name

key: first_name

key_len: 98

ref: const

rows: 1

Extra: Using where

1 row in set (0.01 sec)

The above query is using two single indexes instead of a multi-column index.

Consider the query:

mysql> EXPLAIN SELECT * FROM employees where last_name='tuladhar' or first_name = 'prajwal' \G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: employees

type: index_merge

possible_keys: first_last_name,first_name,last_name

key: last_name,first_last_name

key_len: 98,98

ref: NULL

rows: 2

Extra: Using sort_union(last_name,first_last_name); Using where

1 row in set (0.00 sec)

It is using single column indexes instead of multi-column. That means, Multi column indexes are helpful when you need AND criterial and single column indexes are helpful when you need OR criteria.

PRIMARY KEY AND UNIQUE KEY

Primary Key can’t be NULL while Unique key can be NULL but that column can have only one value of NULL. (a bit weird right? This is because NULL is also a valid data format in MySQL )

FULL TEXT INDEX

I also discussed about this topic in my last post.

When executing a query with varchar and text type and if the column has large number of values, it is generally recommend to use Full Text Index rather than simple pattern matching using LIKE and/or Regular Expression.

AVOID USING SQL EXPRESSIONS WHEN POSSIBLE

This is because SQL expressions can’t take the advantage of indexes. I have taken example from this post:

SELECT * FROM customer WHERE year(customer_join_date) = 2009;

SELECT * FROM customer WHERE customer_join_date BETWEEN '2009-01-01' AND '2009-12-31';

First query can’t take advantage of index while second query can only if `customer_join_date` is defined as index.

References:

• Multi Column indexes vs Index Merge
• 3 ways MySQL uses indexes

Hope this helps you. I would love to hear your feedbacks

If you are a web developer like me then, I guess you are also quite excited about HTML 5 as it opens so many new doors of innovation like native support for SVG and it being tightly integrated with DOM, Canvas API, native RIA components (imagine experiencing multimedia applications without Flash and/or Silver Light) and so on.

Here is a nice video that describe the features of HTML 5 in depth. Do watch it if you want to get into HTML 5.

Introduction to HTML 5 from Brad Neuberg on Vimeo.

For some time, I’ve been digging through Memcached related articles and blog posts over the web in order to understand how people are actually using it. Most of the articles are kinda equivalent. But the use of version in the cache seems more standard and improve form of managing the cache. When, cache keys are managed using version numbers, one does not have to worry about cache conflict issues also that may occur in certain update transactions because Memcached use LRU (Least Recently Used) to remove old cache values.

Here are some links I have gone through:

• http://code.google.com/p/memcached/ (Main resource)
• http://www.linuxjournal.com/article/7451
• http://blog.leetsoft.com/2007/5/22/the-secret-to-memcached
• http://dev.mysql.com/doc/refman/5.1/en/ha-memcached-faq.html
• http://www.igvita.com/2008/04/22/mysql-conf-memcached-internals/
• http://highscalability.com/strategy-break-memcache-dog-pile
• http://abhinavsingh.com/blog/2009/01/memcached-and-n-things-you-can-do-with-it/