MySQL Forums
Forum List  »  Optimizer & Parser

Explain plans for InnoDB and Memory Engines
Posted by: Jagan Kumar
Date: May 02, 2016 03:15PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Explain plans for InnoDB and Memory Engines
1403
May 02, 2016 03:15PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.