How do indexes optimize queries?

To understand how Indexes optimize queries, think of a massive 500-page Physical Book.

If you want to find the chapter on "Data Types":

Without an Index: You have to start at page 1 and read every single page until you find it. In database terms, this is called a Full Table Scan.
With an Index: You flip to the alphabetical Index at the back of the book, find "Data Types," see it's on page 142, and jump straight there.

Here is how that works inside MySQL:

1. Reducing the Search Space

Without an index, MySQL must look at every single row in a table to see if it matches your WHERE clause. If you have 1 million rows, it does 1 million checks. An index is a separate, sorted data structure (usually a B-Tree) that allow MySQL to use "binary search." Instead of checking 1 million rows, it might find your data in only 15 to 20 steps.

2. Avoiding Disk I/O

Reading data from a hard drive is "expensive" (slow).

Indexes are much smaller than the actual table.
Because they are small, MySQL can often keep the entire index in the computer's RAM (Memory).
Searching in RAM is thousands of times faster than searching on a Disk.

3. Faster Sorting (`ORDER BY`)

If you ask MySQL to SELECT * FROM products ORDER BY price;:

No Index: MySQL has to grab all the records and use a "File Sort" algorithm to arrange them, which takes time and CPU.
With Index on price: The index is already stored in order. MySQL simply reads the index from start to finish, and the data is already sorted!

4. Speeding up Joins

When you connect two tables (like orders and products), MySQL uses indexes to quickly match the product_id in the orders table with the id in the products table. Without indexes, Joins become incredibly slow as the database tries to compare every row of Table A with every row of Table B.

The "Cost" of Indexes

If indexes are so great, why not index everything?

Storage: Every index takes up extra disk space.
Slower Inserts/Updates: Every time you INSERT a new row (like you did in the previous step), MySQL has to update the table and update every index associated with that table.

In summary: Indexes make Reading (SELECT) much faster, but they make Writing (INSERT/UPDATE/DELETE) slightly slower.

In your lab, the id column is a Primary Key, which means MySQL automatically created an index for it. That's why searching by id is always super fast!