Is InnoDB Engine up to speed against Memory Engine?

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?

UPDATE:

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 *

It is ironic I answered a question earlier about InnoDB vs MEMORY storage engines.

There is something very weird about the MEMORY Storage Engine you must consider.

MEMORY tables perform full table locks each time there is an INSERT, UPDATE, and DELETE.

MEMORY tables still trigger a little disk I/O because the .frm file of the MEMORY table is a disk file that must be referenced with each query as far the table's existence goes and query parsing thereafter.

The default index type for a MEMORY table is the HASH index not BTREE. If you forget to declare USING BTREE, all range searches become table scans. HASH indexes are poor candidates for indexes to fulfill range queries. The query you have in the question body would quickly be victimized by this.

Even if you create indexing in the MEMORY table with the USING BTREE clause, BTREE indexes in RAM grow at a pace of O(log n) so expect disk I/O again for checking the index definition in the .frm file plus O(log n) running time on page access.

Another crazy thing to think about when using the MEMORY storage engine is this : If you try to join a MEMORY table and an InnoDB table, the resulting lock behavior defaults to the worst one, which in this case is full table locking.

CAVEAT

Others have answered question like this back in March 2011

• David Splllett's answer to What are reasons not to use the MEMORY storage engine in MySQL?

• Morgan Tocker's answer to What are reasons not to use the MEMORY storage engine in MySQL?

Here is one on why an all memory Database is good or bad : Is it feasible to have MySQL in-memory storage engine utilize 512 GB of RAM?

StackExchange Database Administrators Q#11099, answer score: 7