MySql Indexing

MySql Indexing

A database index is a data structure that improves the speed of operations in a table.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.

Indexes are a way to avoid scanning the full table to obtain the result
CREATE TABLE person (
last_name VARCHAR(50) NOT NULL,
first_name VARCHAR(50) NOT NULL,
INDEX (last_name, first_name)
);
So if your index has two columns, say last_name and first_name, the order that you query these fields matters a lot.

This query would take advantage of the index:

SELECT last_name, first_name FROM person WHERE last_name = "John" AND first_name LIKE "J%"

Using Create statement
CREATE INDEX techsudhir_age ON employees(emp_name, emp_age);

Alter command to update index
ALTER TABLE person  ADD INDEX (name);

Removing Indexes
DROP INDEX name ON person 

In MySQL, the full-text index is a kind of index that has a name FULLTEXT. It define for a column whose data type is CHAR, VARCHAR or TEXT
Create FULLTEXT index

CREATE TABLE person (
id int(4) NOT NULL AUTO_INCREMENT,
last_name VARCHAR(50) NOT NULL,
first_name VARCHAR(50) NOT NULL,
post_content text,
PRIMARY KEY (id),
FULLTEXT KEY emp_name(last_name, first_name),
FULLTEXT KEY post_content (post_content)
);

FULL TEXT INDEX

Full-text is a “natural language search”.It indexes words that appear to represent the row, using
the columns you specified.

1. FULLTEXT is the index type of full-text index in MySQL.
2. Full-text indexes can be created only for VARCHAR, CHAR or TEXT columns.

Alter command
ALTER TABLE person ADD FULLTEXT KEY post_content (post_content)
OR you can also create using create command

CREATE FULLTEXT INDEX post_content ON person(last_name, first_name)

ALTER TABLE post_content DROP INDEX keywords;

Syntax : MATCH (col1,col2,col3...) AGAINST (expr [search_modifier])

//AGAINST() takes a string to search, and an optional modifier that indicates what type of search to perform.The search string must be a string value.

SELECT * FROM articles WHERE MATCH(title, body) AGAINST ('PHP');

Types of full-text searches :

Natural Language Full-Text Searches:
Natural language full-text search interprets the search string as a free text and no special operators are required.
Syntax : AGAINST (expr  IN NATURAL LANGUAGE MODE)
Example: SELECT * FROM articles WHERE MATCH(title, body) AGAINST ('PHP' IN NATURAL LANGUAGE MODE);

Boolean Full-Text searches: It allow you to proceed words with a + or a - to force it to either be present (+) or not present (-).
Syntax : AGAINST (expr  (+)exp IN BOOLEAN MODE)
Example: SELECT * FROM articles WHERE MATCH(title, body) AGAINST ('PHP -CakePHP'  IN BOOLEAN MODE);
"-CakePHP" means that MySQL will not return any rows that match "CakePHP", even if they match "PHP".

Putting double quotes around groups of words allow phrase searching. 
Example: SELECT * FROM articles WHERE MATCH(title, body) AGAINST ('"PHP CakePHP"'  IN BOOLEAN MODE);

That matches rows that have "PHP CakePHP" just like that - no words in between, not one or the other.

Querying the Data
mysql> SELECT entryID,title ROM blog_entries WHERE MATCH (title,entry) AGAINST('mother');