MySQL Forums
Forum List  »  MyISAM

Re: Performance problem migrating from NDB to MyISAM
Posted by: Rick James
Date: January 24, 2015 11:39AM

Also, moving to MyISAM is a step backward; move to InnoDB instead.

Specifically, the construct
WHERE x IN ( SELECT ... )
is essentially not optimized and performs very poorly in 5.1. That construct in better optimized in 5.7(?). Meanwhile, turn it into a JOIN.

key_buffer_size (for MyISAM) and innodb_buffer_pool_size (for InnoDB) are not set to reasonable values -- this has a big impact. See
http://mysql.rjweb.org/doc.php/memory

See the following for a number of tips on how to improve the schema when moving to InnoDB from MyISAM. Indirectly, it will give you some tips on your NDB->MyISAM migration:
http://mysql.rjweb.org/doc.php/myisam2innodb

Is this a true/false flag, or just a few small values? If so, use TINYINT (1 byte), not BIGINT (8 bytes):
`processed` bigint(20) unsigned NOT NULL DEFAULT '1'
And use UNSIGNED wherever appropriate.

> where from_unixtime(date) > date('2014-11-30')

Hiding a column (`date`) inside a function call (from_unixtime) prevents using any index on `date`. This has the same effect, and has more potential for efficient use of an index:
where date > '2014-11-30'

The tips we have given you could get you closer to 0.03 secs than 17 sec. However, it may take another pass (of reviewing your settings, schema, engine, version, select, etc) to get all the way to 0.03 sec.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Performance problem migrating from NDB to MyISAM
1491
January 24, 2015 11:39AM


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.