MyISAM is for simple tables and selects due to its architecture faster than
InnoDB. Therefore I changed the engine for this table from
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:
- 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].
- A PHP script is running thousands of the below SELECT statements.
- There are 24 of the above tables in the database which are accessed in the PHP script.
- Each table has ~800k rows.
- 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:
InnoDBengine the script runs 199 seconds.
MyISAMengine 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.
- Why does
InnoDBnot need these indexes and is still faster?
- Is there a better solution?
- And, what did I apparently totally misunderstood as the change to
MyISAMcaused such a horrible performance.