Indexes
With the index in place, the query optimizer can choose to use the index if it is deemed beneficial to do so. If there is more than one index on a table, the optimizer must decide which index will be the most beneficial for a particular SQL statement.
Unique indexes
A unique index plays multiple roles; along with providing all the benefits of a regular index, it also serves as a mechanism for disallowing duplicate values in the indexed column You should not build unique indexes on your primary key column(s), since the server already checks uniqueness for primary key values
Multicolumn indexes
you may also build indexes that span multiple columns.
mysql> ALTER TABLE customer
-> ADD INDEX idx_full_name (last_name, first_name);
Query OK, 0 rows affected (0.35 sec)
Records: 0 Duplicates: 0 Warnings: 0 This index will be useful for queries that specify the first and last names or just the last name, but it would not be useful for queries that specify only the customer’s first name.
Types of Indexes
B-tree indexes
As rows are inserted, updated, and deleted from the customer table, the server will attempt to keep the tree balanced so that there aren’t far more branch/leaf nodes on one side of the root node than the other.
Although B-tree indexes are great at handling columns that contain many different values, such as a customer’s first/last names, they can become unwieldy when built on a column that allows only a small number of values
Bitmap indexes
For columns that contain only a small number of values across a large number of rows (known as low-cardinality data) we will use bitmap.
You would never build a bitmap index on your primary key column, since this represents the highest possible cardinality (a different value for every row).
Bitmap indexes are commonly used in data warehousing environments, where large amounts of data are generally indexed on columns containing relatively few values (e.g., sales quarters, geographic regions, products, salespeople).
Text indexes
If your database stores documents, you may need to allow users to search for words or phrases in the documents.
How Indexes Are Used
To see how MySQL’s query optimizer decides to execute the query, use the explain statement to ask the server to show the execution plan for the query rather than executing the query
mysql>
EXPLAIN
SELECT customer_id, first_name, last_name
FROM customer
WHERE first_name LIKE 'S%' AND last_name LIKE 'P%' \G;
The Downside of Indexes
every index is a table (a special type of table but still a table). Therefore, every time a row is added to or removed from a table, all indexes on that table must be modified. When a row is updated, any indexes on the column or columns that were affected need to be modified as well. Therefore, the more indexes you have, the more work the server needs to do to keep all schema objects up-to-date, which tends to slow things down.