InformIT: MySQL Query Optimization > Using Indexing

Indexing is the most important tool you have for speeding up queries

Highlighted by dorvidas

Nevertheless, if you don't use indexes, in many cases you're just wasting your time trying to improve performance by other means

Highlighted by dorvidas

As just described, indexes are used to speed up searches for rows matching terms of a WHERE clause

Highlighted by dorvidas

rows that match rows in other tables when performing joins

Highlighted by dorvidas

his means that, for the most part, if you don't index your tables, you're hurting yourself

Highlighted by dorvidas

First, indexes speed up retrievals but slow down inserts and deletes

Highlighted by dorvidas

as well as updates of values in indexed columns

Highlighted by dorvidas

That is, indexes slow down most operations that involve writing

Highlighted by dorvidas

This occurs because writing a record requires writing not only the data row, it requires changes to any indexes as well

Highlighted by dorvidas

Index columns that you use for searching, sorting, or grouping, not columns you only display as output

Highlighted by dorvidas

WHERE clause, columns named in join clauses, or columns that appear in ORDER BY or GROUP BY clauses

Highlighted by dorvidas

The cardinality of a column is the number of distinct values that it contains

Highlighted by dorvidas

The conventional wisdom for this percentage used to be "30%

Highlighted by dorvidas

If you're indexing a string column, specify a prefix length whenever it's reasonable to do so

Highlighted by dorvidas

If you're thinking about adding an index to a table that is already indexed, consider whether the index you're thinking about adding is a leftmost prefix of an existing multiple-column index

Highlighted by dorvidas

Match index types to the type of comparisons you perform. When you create an index, most storage engines choose the index implementation they Match index types to the type of comparisons you perform. When you create an index, most storage engines choose the index implementation they will use. For example, InnoDB always uses B-tree indexes. MySQL also uses B-tree indexes, except that it uses R-tree indexes for spatial data types. However, the MEMORY storage engine supports hash indexes and B-tree indexes, and allows you to select which one you want. To choose an index type, consider what kind of comparison operations you plan to perform on the indexed column:

Highlighted by maggie_diigo

Match index types to the type of comparisons you perform. When you create an index, most storage engines choose the index implementation they Match index types to the type of comparisons you perform. When you create an index, most storage engines choose the index implementation they will use. For example, InnoDB always uses B-tree indexes. MySQL also uses B-tree indexes, except that it uses R-tree indexes for spatial data types. However, the MEMORY storage engine supports hash indexes and B-tree indexes, and allows you to select which one you want. To choose an index type, consider what kind of comparison operations you plan to perform on the indexed column:

Highlighted by joel

If you use a MEMORY table only for exact-value lookups, a hash index is a good choice. This is the default index type for MEMORY tables, so you need do nothing special. If you need to perform range-based comparisons with a MEMORY table, you should use a B-tree index instead. To specify this type of index, add USING BTREE to your index definition. For example:

Highlighted by maggie_diigo

If you use a MEMORY table only for exact-value lookups, a hash index is a good choice. This is the default index type for MEMORY tables, so you need do nothing special. If you need to perform range-based comparisons with a MEMORY table, you should use a B-tree index instead. To specify this type of index, add USING BTREE to your index definition. For example:

Highlighted by joel

This log can help you find queries that might benefit from indexing. You can view this log directly (it is written as a text file), or use the mysqldumpslow utility to summarize its contents

Highlighted by dorvidas

Popularity Report

Total Popularity Score: 0

Rank

URL Tag Cloud

Groups (0)

Related Lists

Bookmark History

Public Comment

Public Sticky notes

Site Community

Readers (6)