MySQL Forums
Forum List  »  Optimizer & Parser

Re: Explain plans for InnoDB and Memory Engines
Posted by: Rick James
Date: May 05, 2016 10:18PM

Did both EXPLAINs say "Using index", thereby indicating a "covering" index. That is, the query was performed in the index, without looking at the data.

INDEX(col15,col1) is not the best index for
SELECT DISTINCT col15 from table_1 WHERE col1=2
Instead, have
INDEX(col1,col15)

Your query had to scan the entire index.

Please provide SHOW CREATE TABLE

Note that InnoDB really likes to have a PRIMARY KEY.

Your choice of transactional BEGIN...COMMIT can make a big difference in InnoDB performance. Did you run without such? With what setting of autocommit?

And what about innodb_flush_log_at_trx_commit?

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Explain plans for InnoDB and Memory Engines
790
May 05, 2016 10:18PM


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.