3

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.

black-room-boy
  • 659
  • 2
  • 11
  • 23
  • Please also provide the table structures. Which columns have the been indexed. Whether the columns in `WHERE` clause are indexed or not? – hjpotter92 Oct 07 '15 at 08:17
  • How many rows are totally in the table? How many does COUNT return? – user4035 Oct 07 '15 at 08:27
  • @hjpotter92 - I can not provide full table structures, they are closed for public. First column in `WHERE` clause is indexed, second is not. Both columns are tinyint. – black-room-boy Oct 07 '15 at 08:30
  • @user4035 - There are around 1.2M rows in each table. In InnoDB count returns: 229626 , in old MyISAM I do not know, query is executing for very long time, and table get locked. – black-room-boy Oct 07 '15 at 08:31

2 Answers2

3

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.

user4035
  • 22,508
  • 11
  • 59
  • 94
  • Thank you for answer, and yes it seems that update was blocking my select. I guess that InnoDB will be significant improvement. – black-room-boy Oct 07 '15 at 09:41
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.

Mjh
  • 2,904
  • 1
  • 17
  • 16