I am exploring the efficiency of different database engines in MySQL version 5.5.18 to see which is best suited for use with a range query on a dataset of 5 million rows:
SELECT P.col1, P.col2, P.col3, P.col4, P.col5, P.col6, P.col7, P.col8, P.col9 , P.col10, P.col10 * R.col3 as 'combi' FROM PRODUCT P INNER JOIN RATE R ON R.col2 = P.col2 WHERE P.col3 = 'y' AND P.col4 >= 1000 AND P.col5 >= 5 AND P.col6 BETWEEN 10 AND 100 AND P.col7 >= 0 AND P.col8 >= 7 AND P.col9 >= NOW() AND P.col10 * R.col3 BETWEEN 50 AND 80 ORDER BY P.col8 DESC LIMIT 100;
Based on some discussion on Stackoverflow, I learnt that I may be able to load the database into the RAM by setting InnoDB parameter
innodb_buffer_pool_size larger than the size of the dataset. Following the adjustment of this parameter, I am disappointed that the speed of the query wasn't even faster than MyISAM on most occasions (average of 3s versus 0.3s) and 100 times slower than Memory.
Upon further examination of the MySQL manual, it stated the following two points:
- InnoDB maintains a storage area called the buffer pool for caching data and indexes in memory.
- The larger the buffer pool, the more InnoDB acts like an in-memory database, reading data from disk once and then accessing the data from memory during subsequent reads.
It seems to me that
innodb_buffer_pool_size is more for caching the results of repeated queries than to provide a hot copy of the database from which to run queries. Is my understanding correct or am I missing out something that would allow InnoDB Engine to match Memory Engine, performance-wise for non-repeating range queries such as the one above?
Frederick Cheung pointed out correctly that InnoDB buffer pool does cache a hot copy of the database. What I missed out was a loading procedure found in the link given by Rolando:
Scan the entire contents (all data and index pages) of the table on each startup to preload the content into memory with
SELECT * FROM <table> ORDER BY <pkey fields> for each table followed by
SELECT <indexed fields> FROM <table> ORDER BY <index fields> for each index.
Upon further testing, I am able to bring down the query times (0.3 to 1.0s) to a level comparable to that of MyISAM Engine. But this is still about seven times slower than that obtained with Memory Engine.
Since both engines are querying the database from RAM, can anyone tell me why InnoDB Engine is still not up to speed against Memory Engine?