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
- (sure, many other benefits)
Which fields should be indexed?
- 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.
If we use a function e.g: YEAR() then the index column won’t be used. Use range of full
Multi index column: The order of columns that matters, index(BA) != index(AB) Inequality of operation
When learning some concept you need a sample database.