How come, the number of rows is not the same as SELECT COUNT(*) FROM table

Question

I don't have a lot of experience with DB, but this thing is little confusing: First I did:

mysql> EXPLAIN SELECT COUNT(*) FROM tweets;
+----+-------------+--------+-------+---------------+---------+---------+------+----------+-------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows     | Extra       |
+----+-------------+--------+-------+---------------+---------+---------+------+----------+-------------+
|  1 | SIMPLE      | tweets | index | NULL          | user_id | 4       | NULL | 18683420 | Using index |
+----+-------------+--------+-------+---------------+---------+---------+------+----------+-------------+
1 row in set (0.03 sec)

Than I tried this:

mysql> SELECT COUNT(*) FROM tweets;
+----------+
| COUNT(*) |
+----------+
| 15254792 |
+----------+
1 row in set (9.60 sec)

But the number of rows different from number that I got from last query. Can you please explain why is that? Is it a bug or it is an expected result?

Answer 1

Expected.

EXPLAIN uses statistics about the index to work out a query plan, not the actual index (or data).