This link has been bookmarked by 6 people . It was first bookmarked on 02 Jun 2008, by Darius Orvidas.
-
02 Jul 09
-
01 Jul 09
-
29 Jun 09
-
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: -
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:
-
-
28 Jun 09
-
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: -
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:
-
-
02 Jun 08
-
Indexing is the most important tool you have for speeding up queries
-
Nevertheless, if you don't
use indexes, in many cases you're just wasting your time trying to improve
performance by other means - 14 more annotations...
-
-
As just described, indexes are used to speed up searches for rows
matching terms of a WHERE clause -
rows that match rows in other
tables when performing joins -
his means that, for the most part, if you don't
index your tables, you're hurting yourself -
First, indexes speed up retrievals but slow down inserts and deletes
-
as well
as updates of values in indexed columns -
That is, indexes slow down most
operations that involve writing -
This occurs because writing a record requires
writing not only the data row, it requires changes to any indexes as well -
Index columns that you use for searching, sorting, or grouping, not columns
you only display as output -
WHERE clause, columns
named in join clauses, or columns that appear in ORDER BY or GROUP BY clauses -
The cardinality of a column is
the number of distinct values that it contains -
The conventional wisdom for this percentage used to be
"30% -
If you're indexing a
string column, specify a prefix length whenever it's reasonable to do so -
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 -
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
-
-
Would you like to comment?
Join Diigo for a free account, or sign in if you are already a member.