vivek saini's Profile

like Seam, Spring or Guice for a number of use cases

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