Mysql indexes

1)   Database index is a data structure that improves the speed of operations in a table.

2)   Indexes can be created using one or more columns, providing the basis for both rapid random lookups and efficient ordering of access to records.

3)   INSERT and UPDATE statements take more time on tables having indexes where as SELECT statements become fast on those tables. The reason is that while doing insert or update, database need to insert or update index values as well.

There are four types of statements for adding indexes to a table:
                                           
            1) PRIMARY KEY
            2) UNIQUE 
            3) INDEX or KEY
            4) FULLTEXT                  

SYNTAX :

 ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): This statement adds a PRIMARY KEY, which means that indexed values must be unique and cannot be NULL.

· ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): This statement creates an index for which values must be unique (with the exception of NULL values, which may appear multiple times).

· ALTER TABLE tbl_name ADD INDEX index_name (column_list): This adds an ordinary index in which any value may appear more than once.

· ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list): This creates a special FULLTEXT index that is used for text-searching purposes.

Differences

·       KEY or INDEX refers to a normal non-unique index. Non-distinct values for the index are allowed, so the index may contain rows with identical values in all columns of the index. These indexes don't enforce any restraints on your data so they are used only for making sure certain queries can run quickly.

·       UNIQUE refers to an index where all rows of the index must be unique. That is, the same row may not have identical non-NULL values for all columns in this index as another row. As well as being used to speed up queries, UNIQUE indexes can be used to enforce restraints on data, because the database system does not allow this distinct values rule to be broken when inserting or updating data.

Your database system may allow a UNIQUE index to be applied to columns which allow NULL values, in which case two rows are allowed to be identical if they both contain a NULL value (the rationale here is that NULL is considered not equal to itself). Depending on your application, however, youmay find this undesirable: if you wish to prevent this, you should disallow NULL values in the relevant columns.

·       PRIMARY acts exactly like a UNIQUE index, except that it is always named 'PRIMARY', and there may be only one on a table (and there should always be one; though some database systems don't enforce this). A PRIMARY index is intended as a primary means to uniquely identify any row in the table, so unlike UNIQUE it should not be used on any columns which allow NULL values. Your PRIMARY index should be on the smallest number of columns that are sufficient to uniquely identify a row. Often, this is just one column containing a unique auto-incremented number, but if there is anything else that can uniquely identify a row, such as "countrycode" in a list of countries, you can use that instead.

Some database systems (such as MySQL's InnoDB) will store a table's records on disk in the order in which they appear in the PRIMARY index.

·         FULLTEXT indexes are different from all of the above, and their behaviour differs significantly between database systems. FULLTEXT indexes are only useful for full text searches done with the MATCH() / AGAINST() clause, unlike the above three - which are typically implemented internally using b-trees (allowing for selecting, sorting or ranges starting from left most column) or hash tables (allowing for selection starting from left most column).

Where the other index types are general-purpose, a FULLTEXT index is specialised, in that it serves a narrow purpose: it's only used for a "full text search" feature.

How do you add an index?

To add a correct index, identify the query that executes slowly, and then look at the “where” clause of that query – which fields are used for filtering?  These are probably the ones that should be indexed. For example:

SELECT first_name, last_name FROM contacts WHERE city = “Los Angeles”;

Here, if there isn't already an index on the column “city” (and if the table is more than a few rows), the index should be added:

ALTER TABLE contacts ADD KEY (city);

If you are filtering by multiple fields, like:

SELECT first_name, last_name FROM contacts WHERE status = “active” AND delivery_method = “mail” AND city = “Los Angeles”;

… then you should create a “composite” index:

ALTER TABLE contacts ADD KEY (status, delivery_method, city);

This index will speed up queries that filter by either all of these fields or a subset of them, starting from the left column, as specified in the ALTER TABLE statement.
That means the following where clauses will use the index:

… WHERE status = “active” AND delivery_method = “mail” AND city = “Los Angeles”;
… WHERE status = “active” AND delivery_method = “mail”;
… WHERE status = “active”;

In contrast, where clauses like these will not use this composite index:

… WHERE city = “Los Angeles”;
… WHERE delivery_method = “mail” AND city = “Los Angeles”;


You would have to create another index with the necessary column(s).

Example:

EXPLAIN SELECT first_name, last_name FROM contacts WHERE city = “Los Angeles”;

(I created a small test table consisting of five rows)

mysql> EXPLAIN SELECT first_name, last_name FROM contacts WHERE city = "Los Angeles"\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: contacts
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 5
Extra: Using where
1 row in set (0.00 sec)

As you see here, possible_keys and keys are set to NULL, which means the MySQL optimizer didn’t find any keys to use. An important column in the EXPLAIN output is “rows”, which is an estimate of how many rows MySQL has to read to find the desired result. Here, it is five, which are all rows in the table. That means the database has to perform a full table scan.

Let’s add an index:

ALTER TABLE contacts ADD KEY (city);

That brings us to another important command – you can use “SHOW INDEX FROM <table>” to find out which indexes are already created on a particular table:

mysql> SHOW INDEX FROM contacts\G
*************************** 1. row ***************************
Table: contacts
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 5
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:

*************************** 2. row ***************************

Table: contacts
Non_unique: 1
Key_name: city
Seq_in_index: 1
Column_name: city
Collation: A
Cardinality: 5
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
2 rows in set (0.00 sec)

Here we see two rows – the second row shows the newly added index on the column city. Now if we run the same EXPLAIN-statement again, we get a different – better – result:

mysql> EXPLAIN SELECT first_name, last_name FROM contacts WHERE city = "Los Angeles"\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: contacts
type: ref
possible_keys: city
key: city
key_len: 103
ref: const
rows: 1
Extra: Using where
1 row in set (0.00 sec)


Notice that the possible_keys and keys columns both contain the field “city”, which means that MySQL was able to use this index to satisfy the query. Another important difference is the number of rows MySQL estimates it will have to search – it’s now only one!  The database is now going directly from the index to the row (which is referenced by the primary key).

Storage engines:

MySQL supports several storage engines that act as handlers for different table types. MySQL storage engines include both those that handle transaction-safe tables and those that handle non-transaction-safe tables:

MYISAM:

1.    MYISAM supports Table-level Locking

2.    MyISAM designed for need of speed

3.    MyISAM does not support foreign keys hence we call MySQL with MYISAM is DBMS

4.    MyISAM stores its tables, data and indexes in diskspace using separate three different files. (tablename.FRM, tablename.MYD, tablename.MYI)

5.    MYISAM not supports transaction. You cannot commit and rollback with MYISAM. Once you issue a command it’s done.

6.    MYISAM supports fulltext search

7.    You can use MyISAM, if the table is more static with lots of select and less update and delete.

mysql> SHOW ENGINES\G
*************************** 1. row ***************************
Engine : MyISAM
Support: DEFAULT
Comment: Default engine as of MySQL 3.23 with great performance

     INNODB :

1.    InnoDB supports Row-level Locking

2.    InnoDB designed for maximum performance when processing high volume of data

3.    InnoDB support foreign keys hence we call MySQL with InnoDB is RDBMS

4.    InnoDB stores its tables and indexes in a tablespace

5.    InnoDB supports transaction. You can commit and rollback with InnoDB

mysql> SHOW ENGINES\G
*************************** 1. row ***************************
Engine : InnoDB
Support: YES
Comment: Supports transactions, row-level locking,and foreign keys