Does Higher Rows Count In Mysql Explain Means Good Or Bad?

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

Solution 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.

Solution 2:

The row count tells you how many rows MySQL had to inspect in order to obtain the result for your query. This is where indexes help and where a number called index cardinality plays a very high role. Indexes help MySQL cut down on inspecting rows so the less it does - the faster it is. Since there are many rows that satisfy your condition of vrsta_dokumenta = 3 AND dostupnost = 0 then MySQL simply has to go through these, find them and increment the counter. For MyISAM that means MySQL has to read the data from the disk - this is expensive because disk access is very slow. For InnoDB it's extremely quick because InnoDB can store its working data set in memory. In other words, MyISAM reads from disk while InnoDB will read from memory. There are other optimizations available, but general rule is that InnoDB will be quicker than MyISAM is, even with table growth.