Mysql – MyISAM around 5x slower than InnoDB

innodbmyisamMySQLperformance

I thought, MyISAM is for simple tables and selects due to its architecture faster than InnoDB. Therefore I changed the engine for this table from InnoDB to MyISAM:

CREATE TABLE `table1` (
  `DateTime` datetime NOT NULL,
  `BidHigh` decimal(11,5) NOT NULL,
  `BidLow` decimal(11,5) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin COMMENT='1 minute rates';

ALTER TABLE `table1` ADD PRIMARY KEY (`DateTime`);

These conditions apply:

  1. I am testing it on a SLES 15.1 VM with 5 GB RAM and 8 CPU cores on a host which is mine and it doesn't have to serve other VM [just an information that there is no other VM which may influence the result].
  2. A PHP script is running thousands of the below SELECT statements.
  3. There are 24 of the above tables in the database which are accessed in the PHP script.
  4. Each table has ~800k rows.
  5. Between each test I restart the server to assure there are always the same conditions. Due to the fact the difference is so huge I am not running multiple test to get an average…

The execution time:

  • With InnoDB engine the script runs 199 seconds.
  • With MyISAM engine the script runs 1'026 seconds. More than 5 times longer.

I am running these SELECT statements:

SELECT `DateTime` FROM table1
     WHERE `DateTime` BETWEEN '2018-12-27 07:50:00' AND '2199-12-31 23:59:00'
        AND BidHigh > 0.96604
     ORDER BY `DateTime` LIMIT 1;

— and —

SELECT MIN( BidLow ) FROM table1
    WHERE `DateTime` BETWEEN '2018-12-27 07:45:00' AND '2199-12-31 23:59:00';

OK, I figured out it is an index problem. Adding these two indexes

ALTER TABLE `table1` ADD UNIQUE `BidHigh` (`DateTime`, `BidHigh`);

— and —

ALTER TABLE `table1` ADD UNIQUE `BidLow` (`DateTime`, `BidLow`);

fixes the performance issue and now the script needs 245 seconds, still slower than InnoDB – this is not really what I expected…

Adding these indexes to the InnoDB version doesn't improve the performance.

My questions:

  1. Why does InnoDB not need these indexes and is still faster?
  2. Is there a better solution?
  3. And, what did I apparently totally misunderstood as the change to MyISAM caused such a horrible performance.

Best Answer

1) InnoDB will go faster with indexes, too.

2) InnoDB with appropriate indexing is the best solution.

3) MyISAM has been slower than InnoDB for most workloads for well over a decade. There are fundamental difference in how memory and caching work between the two.

In this case InnoDB was picking the first row by primary key. Since InnoDB tables are clustered by primary key, this was very fast, and PK was likely already in memory from when you created the table.

Create an index in BidHigh and it'll go even faster.

Unless you have an overwhelmingly good reason to use MyISAM - you shouldn't. And if you think you have an overwhelmingly good reason, you should re-scrutinize it because they are vanishingly few in 2020.

Related Question