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?