InformIT: MySQL Query Optimization > Using Indexing - The Diigo Meta page

• 26 Nov 09
  

  vivek saini
  • e circumstances under which indexes might
    degrade performance and provides guidelines for choosing indexes for your table
    wisely
  • an unordered collection of rows
  • 20 more annotations...
  • • 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
  

  Navneet Kumar

  MySQL Performance Optimization

• 01 Jul 09
  

  alfred westerveld

  mysql index Btree informit

• 29 Jun 09
  

  Joel Liu

  Mysql index Btree

  • 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
  

  Maggie Tsai

  Mysql index Btree

  • 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
  

  Darius Orvidas

  mysql indexes perfomance

  • 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
    • 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

Would you like to comment?

Join Diigo for a free account, or sign in if you are already a member.