Warren Buffett and Bill Gates: Keeping America Great

No doubt, visualization is the best way to extract effectiveness of any data set.

And if you love visualized data, you will definitely love this:

Found via

Coders at Work has to be one of the most read books for programmers and/or software developers of all levels. Among 15 sets of Q/A with most probably great programmers of all time, I want to share paragraph of a conversation with Brad Fitzpatrick, creator of Memcached and co-founder of LiveJournal.

The one way they think about solving things is always within the space they know. They don’t think end-to-end as much. I think it’s really important to know the whole stack even if you don’t operate within the whole stack.

Polyglotism is a way to go!!!

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/

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)

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)

The demo for WebGL API looks awesome.

Source