This link has been bookmarked by 33 people . It was first bookmarked on 02 Jun 2008, by Darius Orvidas.
-
05 Jun 13
-
21 Apr 12
-
27 Sep 11
Jochen FrommDatabase management systems implement abstract concepts but do so on real hardware bound by real physical constraints. As a result, queries take time—sometimes an annoyingly long time.
mysql optimization performance database indexing query sql tips tricks
-
24 Jul 11
-
10 Mar 11
-
01 Mar 11
-
09 Aug 10
-
16 Jun 10
-
26 Nov 09
-
e circumstances under which indexes might degrade performance and provides guidelines for choosing indexes for your table wisely
-
an unordered collection of rows
-
This involves a full table scan
-
index entries are sorted by company_num value
-
Index values are sorted,
-
Another efficiency comes about through the use of positioning algorithms for finding the first matching entry without doing a linear scan from the start of the index (for example, a binary search is much quicker than a scan)
-
Using indexes as entities separate from the data rows solves the problem and allows multiple indexes to be created
-
The particular details of index implementations vary for different MySQL storage engines
-
Indexes are even more valuable when you're running queries involving joins on multiple tables
-
The query runs about a million times faster this way—literally
-
This occurs because writing a record requires writing not only the data row, it requires changes to any indexes as well. The more indexes a table has, the more changes need to be made, and the greater the average performance degradation.
-
candidate columns for indexing are the columns that appear in your WHERE clause
-
columns named in join clauses, or columns that appear in ORDER BY or GROUP BY clauses.
-
that is, columns that have many unique values and few duplicates
-
A clustered index is one where the data rows are stored together with (that is, clustered with) the primary key values
-
Other indexes are secondary indexes;
-
The implication is that primary key values are duplicated into each secondary index
-
A composite index serves as several indexes because any leftmost set of columns in the index can be used to match rows.
-
Every additional index takes extra disk space and hurts performance of write operations, as has already been mentioned
-
Creating extra indexes creates more work for the query optimizer
-
It's also possible (if unlikely) that MySQL will fail to choose the best index to use when you have too many indexes.
-
InnoDB always uses B-tree indexes
-
-
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:
-
-
19 May 09
-
15 Sep 08
-
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
-
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
-
This occurs because writing a record requires writing not only the data row, it requires changes to any indexes as well
-
That is, indexes slow down most operations that involve writing
-
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
-
-
12 Feb 08
-
08 Feb 08
Would you like to comment?
Join Diigo for a free account, or sign in if you are already a member.