How MySQL Uses Indexes?
Indexes are associated with a specific column. They help in speeding up search operations.if an index is not present, MYSql has to search each and every row, if an index is present, and the corresponding row can be looked. Most of the indexes like primary key, unique, index are stored in B TREES.
Example:Following select query is fired:
Select * from table_name where col1=value1 AND col2=value2;
If a multiple-column index exists on col1 and col2, the appropriate rows can be fetched directly.
State how to create and drop indexes in MySQL.
Index can be created by assigning it a unique name on a column.
Create Index:CREATE INDEX index_name
ON table_name (col_1,col_2..);
Example:Create index index_sample
ON employee(emp_id)
Delete index:drop index [schema.]index [force];
Example:Drop index new_sample
What are the purposes of MySQL indexes?
Indexes help us to find data faster. It can be created on a single column or a combination of columns. A table index helps to arrange the values of one or more columns in a specific order.
Purpose:1. Allow the server to retrieve requested data, in as few I/O operations
2. Improve performance
3. To find records quickly in the database
State the tips to optimize MySQL Indexes.
1. The columns with the most unique and variety of values should be used.
2. Smaller the index better the response time.
3. For functions that need to be executed frequently, large indexes should be used.
4. Avoid use of index for small tables.
Brief about Column Indexes with an example.
Column indexes can be used on a column of almost any data type when trying to improve performance on SELECT operation. Almost 16 indexes per table can be used.using column_name(N), index can be created that uses only the first N characters of a string column.
Example:CREATE TABLE sample (sample_id BLOB, INDEX(sample_id(10));
Brief about Multiple-Column Indexes with an example.
A multi column index can be used on multiple columns. An index can contain up to 15 columns. Multi column indexes are used in such a way that queries are fast when a known quantity is specified for the first column of the index in a WHERE clause, even if values for the other columns are not specified.
Example:CREATE TABLE employee (
Id INT NOT NULL,
First_name CHAR(30) NOT NULL,
Last_name CHAR(30) NOT NULL,
PRIMARY KEY (id),
INDEX name (last_name,first_name)
);
Here, the index is not used if first name’s value is specified in the query, i.e.
Select * from employee where first_name =’john’;
