MySQL Index

Created with Sketch.

MySQL Index

MySQL uses indexes to quickly find rows with specific column values. Without an index, MySQL must scan the whole table to locate the relevant rows. The larger table, the slower it searches.

In this section, you will learn about MySQL index including creating indexes, removing indexes, listing all indexes of a table and other important features of indexes in MySQL.

  • Creating indexes  – introduces you to the index concept and shows you how to create an index for one or more columns of a table.
  • Removing indexes – shows you how to remove an existing index of a table.
  • Listing table indexes – provides you with a statement to list all indexes or specific indexes of a table.
  • Unique indexes – uses the unique indexes to ensure distinct values stored in a column.
  • Prefix indexes – shows you how to use the prefix index to create an index for a character string column.
  • Invisible indexes – covers the index visibility and shows you how to make an index visible or invisible.
  • Descending indexes –  shows you how to use descending indexes to increase query performance.
  • Composite indexes – illustrates the application of composite indexes and shows you when to use them to speed up your queries.
  • Clustered indexes – explains the clustered indexes in InnoDB tables.
  • Index cardinality – explains the index cardinality and shows you how to view it using the show indexes command.
  • USE INDEX hint –  shows you how to use the USE INDEX hint to instruct the query optimizer to use the only list of specified indexes to find rows in a table.
  • FORCE INDEX hint – shows you how to use the FORCE INDEX hint to force the query optimizer to use specified indexes to select data from a table.

Leave a Reply

Your email address will not be published.