What is Index?

Indexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first row and > then read through the entire table to find the relevant rows.

What is B-Tree?

It is a self-balancing search tree.

When to use index?

  • WHERE clause quickly.
  • When having multiple indexes, MySQL uses the index that finds the smallest number of rows.
  • Index won’t work if incorrect data type, for example, you index email column but when you do the query like that
1
select * from users where email=123
  • (sure, many other benefits)

https://dev.mysql.com/doc/refman/8.0/en/mysql-indexes.html

Which fields should be indexed?

  • WHERE
  • JOIN
  • SEARCH (WHERE last_name LIKE “a%")

What happened to the field when it is indexed?

  • The field data will be put on RAM, so its faster to access.
  • PRIMARY KEY, UNIQUE, INDEX, FULLTEXT are stored in B-Tree.

Why we don’t index all the columns?

If the row is updated then the index also need to be updated.

  • Indexing makes reading faster but writing slower!.
  • Data < 1000 rows, index won’t help.
  • Indexes eat ram, disk space.

Common pitfalls

  • If we use a function e.g: YEAR() then the index column won’t be used. Use range of full datetime instead

  • Multi index column: The order of columns that matters, index(BA) != index(AB) Inequality of operation

Sample databases

When learning some concept you need a sample database.

Reference: