How To Optimize MySQL Queries Anderson SC

Using indexes

Using indexes makes the query faster in the same way as an index in a book helps in looking for a particular word.

To know how and which indexes are used in a given query, use EXPLAIN command as a prefix to select query.

EXPLAIN SELECT emp_id FROM ORGN_DATA WHERE dept_code = 'IT';

This command returns following information:

Table - Which table(s) are used in the query
Type - JOIN type. Values can be system, const, eq_ref, ref, range, index, all
Possible Keys - All keys which can be used for indexes
Key - The key actually used for index
Key Length - Shorter keys are better
Ref - Other Columns used with key to get the results
Rows - Number of rows from where data will come
Extra - Extra information. Some possible values can be using index, using where, using temporary, using filesort

You should reconsider your table structure if Possible Keys contain NULL value.
A NULL value in Key field indicates no index is being used. Using no indexes will deter the performance if there are too many records in the table because, for any query, all the records will be searched for the given condition.

Use ALTER command to add index as given below:

ALTER TABLE table_name ADD INDEX (column_name);

You can do indexing on multiple columns using:

ALTER TABLE table_name ADD INDEX (column_name1,..,column_nameN);

MySQL uses leftmost prefixing, so automatically indexes will be added for column_name1, (column_name1,column_name2),...(column_name1,..,column_NameN-1). This is quite helpful in situations like searching based on surname so an index on (surname, firstname, middlename) will automatically add multiple indexes used in faster querying.

Points to remember:

• Choose the column(s) for indexing very carefully....

  Click here to read the rest of the article at HowToDoThings.com

  Author: Syed Feroz Zainvi