Explain plans for InnoDB and Memory Engines

[Jagan Kumar]

Hello All,

We are trying to experiment MySQL memory engine features compared to InnoDB
as part of a performance analysis.

- The table has 3 million records and 15 columns
- Read performance from the table when it is based on InnoDB Engine
- Read performance from the table when it is based on Memory Engine
- Index are the same in both settings (all are B-Tree)
- A composite B-Tree is deined on (col15,col1)

Following is one of the simple SQLs used in the comparision.
SELECT DISTINCT col15 from table_1 WHERE col1=2


One of the interesting areas is the explain plans selected by the optimizer.

- Explain plan based on the InnoDB engine is more efficient and "TYPE"=range
- Explain plan based on the Memory engine is less efficient and "TYPE"=ref
- Elapsed time in MEMORY setting SQL is taking 3 times of the elapsed time in
InnoDB setting


Basically, the behavior of queries change when move to Memory engine from InnoDB
and the performance gain expected out of Memory engine is NOT always correct.

Kindly share your thoughts on this.

Thanks,
Jagan