↓Skip to main content
Thanh’s Notes

MySQL Index

·2 mins

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
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: #

↑