On when MySQL uses indexes, when not using indexes

Index:

Use an index to quickly access specific information in a database table. An index is a structure that sorts the values of one or more columns in a database table, such as the last Name column of an employee table. If you want to find a specific employee by last name, the index will help you get that information faster than if you have to search all the rows in the table.

An index is a separate, physical database structure that is a collection of one or more column values in a table and a logical pointer list corresponding to the data pages that physically identify those values in the table.

The index provides pointers to the data values stored in the specified columns in the table, and then sorts the pointers according to the sort order that you specify. The database uses the index in the same way that you use an index in a book: it searches the index to find a specific value, and then follows the pointer to the row that contains the value.

In a database diagram, you can create, edit, or delete each index type in the Indexes/Keys property page of the selected table. The index is saved in the database when you save the table to which the index is attached, or when you save the diagram where the table is located.

Attention:

Not all databases use the index in the same way. As a general rule, you need to create an index on a table only when you frequently query the data in an indexed column. Indexes consume disk space and reduce the speed at which rows are added, deleted, and updated. In most cases, the speed advantage of indexing for data retrieval greatly outweighs its shortcomings. However, if your application updates data very frequently or if disk space is limited, you may want to limit the number of indexes.

You can create indexes based on single or multiple columns in a database table. Multi-column indexes allow you to distinguish between rows in which one column might have the same value.

Indexes are also helpful if you frequently search for two or more columns or sort by two or more columns at the same time. For example, if you frequently set criteria for a first and last name in the same query, it would make sense to create a multicolumn index on those two columns.

To determine the validity of an index:

• Check the WHERE and JOIN clauses of the query. Each column included in either clause is an object that the index can select.
• Experiment with the new index to check its impact on running query performance.
• Consider the number of indexes that have been created on the table. It is best to avoid having many indexes on a single table.
• Checks the definition of an index that has been created on the table. It is best to avoid overlapping indexes that contain shared columns.
• Checks the number of unique data values in a column and compares the number to the number of rows in the table. The result of the comparison is the selectivity of the column, which helps to determine if the column is suitable for indexing and, if appropriate, the type of the index.

When MySQL uses the index

Use of a key code, >=, =, <, <=, IF null and between

1. SELECT * FROM table_name WHERE key_part1=1 and key_part2 > 5;
2. SELECT * FROM table_name WHERE key_part1 is NULL;

When using a like that does not begin with a wildcard character

1. SELECT * FROM table_name WHERE key_part1 like ' jani% '

Fetching rows from another table while the junction is in progress

1. SELECT * from t1,t2 where T1.col=t2.key_part

Finds the Max () or min () value of the specified index

1. SELECT MIN (key_part2),MAX (KEY_PART2) from table_name where key_part1=10

Prefix of a key code using order by or GROUP by

1. SELECT * from foo ORDER by key_part1,key_part2,key_part3

At all the columns used in the query are part of the time of the key code

1. SELECT KEY_PART3 from table_name WHERE key_part1=1

When does MySQL not use the index

If MySQL can estimate that it will probably be faster than scanning the entire table, then the index is not used. For example, if the key_part1 is evenly spaced between 1 and 100, the index used in the following query is not very good:

1. SELECT * FROM table_name where Key_part1 > 1 and key_part1 <

If you are using the heap table and do not use = Search all key parts.

Use order by on the heap table.

If you're not using the key code, the first part

1. SELECT * FROM table_name WHERE key_part2=1

If you use a like that starts with a wildcard character

1. SELECT * FROM table_name WHERE key_part1 like '%jani% '

Search for an index and do an order by on another index

1. SELECT * FROM table_name WHERE key_part1 = # ORDER by key2

On when MySQL uses indexes, when not using indexes