Does higher rows count in mysql explain means good or bad?

Question

I have one old MyISAM table where when I submit some count query, table gets locked. If I do the same query, on the same InnoDB table, query gets executed fast. The problem is, the old MyISAM table is still used in production and is under heavy load, while the new one is not.

Now we come to my problem and question. When I do explain on query's executed in both tables I get some result that confuses me.

Here is the query that I am executing in both tables :

SELECT COUNT(*)
FROM table
WHERE vrsta_dokumenta = 3
    AND dostupnost = 0

Here is the explain from the old MyISAM table:

id select_type table     type possible_keys         key               key_len ref    rows   Extra    
1  SIMPLE     old_table  ref idx_vrsta_dokumenta idx_vrsta_dokumenta     1   const 564253  Using where 

And here is the explain from the new InnoDB table:

id select_type   table  type    possible_keys         key           key_len  ref    rows   Extra     
1  SIMPLE      new_table ref idx_vrsta_dokumenta idx_vrsta_dokumenta   1    const 611905 Using where

As you can see the rows count in new table is higher than in old.

So in the case that higher number is bad, does this mean that query on the new table will be slower once it is fully in use ?

In case the higher number is good, then maybe that is the reason why new table is faster, and MyISAM gets locked after some time of execution.

Anyway, what is correct? What does this rows count mean?

EDIT: the old table has twice more columns than the new one. Since the old is has been split into 2 tables.

Answer 1

black-room-boy:

So in the case that higher number is bad, does this mean that query on the new table will be slower once it is fully in use?

MySQL manual says about the rows column in EXPLAIN:

The rows column indicates the number of rows MySQL believes it must examine to execute the query.

For InnoDB tables, this number is an estimate, and may not always be exact.

So, the higher number is not bad, it's just a guess based upon table metadata.

black-room-boy:

In case the higher number is good, then maybe that is the reason why new table is faster, and MyISAM gets locked after some time of execution.

Higher number is not good. MyISAM get's locked not because of this number.

Manual:

MySQL uses table-level locking for MyISAM, allowing only one session to update those tables at a time, making them more suitable for read-only, read-mostly, or single-user applications.

... Table updates are given higher priority than table retrievals... If you have many updates for a table, SELECT statements wait until there are no more updates.

If your table is frequently updated, it get's locked by INSERT, UPDATE, and DELETE (a.k.a. DML) statements, this blocks your SELECT query.