optimizing performance
Despite the clear advantages of using indexes, there are some drawbacks. For example, indexes can require a great deal of storage. The larger the tables and greater the number of indexes, the more storage you need to hold those indexes. You must allow for file size and potential growth whenever you implement an index. Another disadvantage to indexes is that, while they speed up data retrieval, they can slow down data inserts and deletes, as well as updates to columns that are indexed. Any change made to rows in an indexed table must also be made to the index(unless the change is an update that doesn't affect the value in the index).
Despite these drawbacks, indexing provides the most beneficial tool for improving the performance of your SELECT statements. You should not, however, index every column in a table. The following list provides several guidelines that you can use in determining when to implement indexing:
Index columns that appear in search conditions. As a general rule, you should consider defining an index on any column that you commonly use in WHERE, GROUP BY, or HAVING clauses. Because these columns define the limitations of a query, they are good candidates for improving performance because they allow MySQL to identify quickly which rows should be included in a search and which should not.
Index columns that appear in join conditions. Index any columns that appears in a join condition. Because join conditions are often based on foreign key columns that reference primary key columns, MySQL creates the indexes automatically when you define the primary keys and foreign keys.
Do not index columns that appear only in the SELECT clause. If a column appears in the SELECT clause of a SELECT statement, but does not appear in WHERE, GROUP BY, or HAVING clauses, you usually shouldn't index these columns because indexing them provides no performance benefit but does require additional storage. Indexing columns in the SELECT clause provides no benefit because the SELECT clause is one of the last parts of a SELECT statement to be processed. MySQL conducts searches based on the other clauses. After MySQL identifies which rows to return, it then consults the SELECT clause to determine which columns from the identified rows to return.
Do not index columns that contain only a few different values. If a column contains many duplicated values, indexing that column provides little benefit. For example, suppose that your column is configured to accept only Y and N values. Because of the way in which MySQL accesses an index and uses that index to locate the rows in the tables, many duplicated values can actually cause the process to take longer than if no index is used. In fact, when MySQL finds that a value occurs in more than 30 percent of a table's rows, it usually doesn't use the index at all.
Specify prefixes for indexes on columns that contain large string values. If you're adding an index to a string column, consider defining a prefix on that index so that your index includes only part of the entire values, as they're stored in the table. For example, if your table includes a CHAR(150) column, you might consider indexing only the first 10 or 15 bytes, or whatever number provides enough unique values without having to store the entire values in the index.
Create only the indexes that you need. Never create more indexes than you need. If a column is rarely used in a search or join condition, don't index that column. You want to index only those columns that are frequently used to identity the rows being searched.
Primary key:
Foreign key:
Regular: Provides a basic index that permits duplicate values and NULL values in the columns on which the index is defined.
Unique: Requires that each value set of values be unique in the columns on which the index is defined. Unlike primary key indexes, NULL values are allowed.
Full-text: Supports full-text searches of the values in the columns on which the index is defined. A full-text index permits duplicate values and NULL values in the those columns. A full-text index can be defined only on MyISAM tables and only CHAR, VARCHAR, and TEXT columns.
