Mysql brain teaser: a simple query checks all rows

I’ve seen this in many queries on many tables that take this form. It baffles me. Are you good enough at mysql to figure this brainteaser out? Coz I’m (still) not.

EXPLAIN SELECT *
FROM `video`
ORDER BY id
LIMIT 0 , 30

(id is the primary key, it might also be another index, makes no difference in this case.)

With an explain, I get:
key: primary (this is used to order it)
rows: 309973 (mysql estimates it needs to go over *all* the rows in the table).

So if I interpret this correctly, mysql needs to go over all rows for this query? That’s crazytalk!

Any ideas? Maybe mysql doesn’t *really* go over all those rows, it just thinks it needs to? I don’t know anymore…

0 thoughts on “Mysql brain teaser: a simple query checks all rows

  1. My excellent Dreamhost support person says, basically: mysql is lying. It’s not actually going over all those rows that it reports in EXPLAIN. Pfew!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s