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.

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)

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

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/

Full text index is a special type of index that can be used to search words data field in a table.

According to MySQL documentation:

Full-text indexes can be used only with MyISAM tables, and can be created only for CHAR, VARCHAR, or TEXT columns.

And here is an excerpt from High Performance MySQL:

A full-text index is a special type of index that can quickly retrieve the locations of every distinct word in a field. MySQL’s provides full-text indexing support in MyISAM tables. Full-text indexes are built against one or more text fields (VARCHAR, TEXT, etc.) in a table.

The full-text index is also stored in a table’s .MYI file. It is implemented by creating a normal two-part MyISAM B-tree index in which the first field is a VARCHAR, and the second is a FLOAT. The first field contains the indexed word, and the FLOAT is its local weight in the row.

Whereas like is a standard SQL term used for matching pattern in the query. In MySQL, you can also match any arbitrary characters using ‘%’ or you can also use regular expression. But use of like when matching a word in a query is quite costly in terms of time than using Full Text Index.

Example:

mysql> describe students;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255) | NO   | MUL | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> select * from students where name like '%prajwal%';
+----+------------------+
| id | name             |
+----+------------------+
|  1 | Prajwal Tuladhar |
|  2 | PrAJwal TUladhar |
+----+------------------+
2 rows in set (9.93 sec)

mysql> select * from students where match (name) against ('prajwal');
+----+------------------+
| id | name             |
+----+------------------+
|  1 | Prajwal Tuladhar |
|  2 | PrAJwal TUladhar |
+----+------------------+
2 rows in set (0.08 sec)

Just look at the difference in the time. When full text is used, it only took 0.08 sec while query with like took whooping 9.93 sec. The logic is simple because, indexing the column generally helps to execute the query when using relative criteria. Only disadvantage of using Full Text is that the indexes tend to get larger quickly as they contain one record for each word in each indexed field. It may also depend on which should be given more importance: space or query execution time.

When using like, MySQL has to go through each and every row. And when regular expression is use as pattern matching, MySQL optimizer will never try to optimize the query resulting dismal performance.

Update: I forgot to show the number of rows of the table `students`.

mysql> select count(*) from students;
+----------+
| count(*) |
+----------+
|  1073052 |
+----------+
1 row in set (0.76 sec)

I learned something new today regarding MySQL.

The use of binary operator if used in a query or as collation type in DDL can make some significant difference in the results. Generally, when we create some table in MySQL, we don’t use binary collation. For example:

CREATE TABLE students (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL
)ENGINE=MYISAM CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Now Lets insert some data

INSERT INTO stduents set name = 'Prajwal Tuladhar';
INSERT INTO stduents set name = 'PrAJwal TUladhar';

If a query is run in the above table:

mysql> SELECT *
    -> FROM students
    -> WHERE name = 'Prajwal Tuladhar';
+----+------------------+
| id | name             |
+----+------------------+
|  1 | Prajwal Tuladhar |
|  2 | PrAJwal TUladhar |
+----+------------------+
2 rows in set (0.00 sec)

So, if we want case sensitive results from a case insensitive table as above:

mysql> SELECT *
    -> FROM students
    -&gt; WHERE <strong>binary</strong> name = 'Prajwal Tuladhar';
+----+------------------+
| id | name             |
+----+------------------+
|  1 | Prajwal Tuladhar |
+----+------------------+
1 row in set (0.00 sec)

Or we can just changed the collation of the table as:

ALTER TABLE `students`  DEFAULT CHARACTER SET utf8 COLLATE utf8_bin

More information about binary operator here.