How to create index in MySql table.
CREATE INDEX index_name ON table_name (column_list)
CREATE INDEX jobTitle ON employees(jobTitle);How to remove Index form a table.
DROP INDEX index_name ON table_name
DROP INDEX name ON leads;Show index from a table.
SHOW INDEXES FROM table;
SHOW INDEXES FROM employees;
Before query optimization, we usually use EXPLAIN keyword before the query string. This EXPLAIN keyword gives us all the necessary information about that query. Among all the information Access type(type) very important info which is needed to know all the developers. So let see what is Access type.
Access type tells us, how your query accesses all the data from the dataset when you will execute the query. There are 6 types of access type values you will find. when you will put EXPLAIN keyword Before your Query string.
CONST/EQ_REF
Both work the same way so that I treat both the same types. The database is performing a B-Tree traversal to find a single value. Basically is a binary search. It’s only applicable when you have a unique data set in your result. another way you can say you get at most 1 row in your result. Two way you can do it
1. Primary key in your column.
2. You have a unique constraint on that column.
So lastly when you found this value in access type just skip no need to optimize. Because it’s not going to faster than this. it’s super fast.
REF/RANGE
They are known as index range scans. Perform a B-Tree traversal to find the starting point. after that, its scans value from that point on. It actually limits the total number of the row the database has to inspect to perform your query. It’s a good thing.
INDEX
Its also known as a Full index Scan. Index scan still using the index but its not using the limit. Its scan from at the first row and to the last row. Their is no filtering in to the index you just scan all the rows.
ALL
And lastly, All is also known as a Full Table Scan. You just load all rows into the memory not using any index. It’s a very bad access type. we should not expect this type.