|The main use of indexes is to speed up queries. Most MySQL indexes are BTrees. They're efficient for single lookups and range scans. |
The two main MySQL storage engines, InnoDB and MyISAM, use BTrees differently. InnoDB clusters row data with its primary key (PK) or unique key, so a PK lookup finds row data right there in the index. A "secondary" or covering index contains the covering index's column values and the PK, which it uses to find matching row data. Each MyISAM index, though, is simply a separate BTree with pointers into the data file. These differences matter: if a query filters a table on
Per query clause, to avoid a full table scan, the optimiser looks for an index whose selectivity is adequate (boolean values have least), and whose columns the
Index column order is important. Given index(lnam,fnam),
If the query has a
It's often thought that indexes on small tables are unimportant, but if a table is accessed repeatedly, for example in a function call on a big table, seemingly insignificant seek times can add up to cumulative times of minutes or hours.
Useful articles on indexes & queries ...