Why query is still so fast when I operate a non-indexing column?

I am learning indexing of database.

here are indexings of a table. And this table has 330k records.

mysql> show index from employee;
+----------+------------+-------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name    | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+-------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| employee |          0 | PRIMARY     |            1 | id            | A         |      297383 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| employee |          0 | ak_employee |            1 | personal_code | A         |      297383 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| employee |          1 | idx_email   |            1 | email         | A         |      297383 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+----------+------------+-------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

as you can see, there are only three indexing on this table.

Now I want to query with where on birth_date column, I think it will be very slow because there is no indexing on birth-date column, I when I try query, I found it is very fast.

mysql> select sql_no_cache *
    -> from employee
    -> where birth_date > '1955-11-11'
    -> limit 100
    -> ;

100 rows in set, 1 warning (0.04 sec)

So I am confused:

why it is still so fast without indexing?
due to its still fast, why do we still need indexing?

Answer

This is your query:

select sql_no_cache *
from employee
where birth_date > '1955-11-11'
limit 100

There are no indexes so the query starts reading the data from the data pages. On each record, it compares the birthdate and returns the row. When it finds 100 (due to the limit) it stops.

Presumably, it finds 100 rows quite quickly. After all, the median age of the United States is about 38 — which is (as I write this) a birth year of 1981. By far, most people were born after 1955.

The query would be much slower if you had an order by or group by. That would require reading all the data before returning anything.

Advertisement

Answer