Return to Answer

Commonmark migration

Source Link

edited Jun 15, 2020 at 9:05

Community Bot

Here are some gotchas

#Memory Usage

Memory Usage

##MyISAM

MyISAM

only caches index pages.
shared keycache (sized by key_buffer_size).
You can also set up dedicated keycache, one or more tables per cache table.

##InnoDB

InnoDB

caches data pages and index pages.
one buffer pool and one size before MySQL 5.5
1 or more buffer pools starting with MySQL 5.5

Here are some queries I wrote and posted earlier on how to choose a proper size for the MyISAM Key Cache and InnoDB Buffer Pool.

#FULLTEXT Indexes

FULLTEXT Indexes

##MyISAM

MyISAM

Supports FULLTEXT indexes

##InnoDB

InnoDB

Starting with MySQL 5.6, yes, but still in beta (UPDATE: MySQL 5.6 exists and has FULLTEXT indexes. If you are using FULLTEXT indexing in MySQL 5.6, make sure you are using the InnoDB-specific FULLTEXT options)
Before MySQL 5.6, This means you cannot convert MyISAM to InnoDB.

##MySQL 5.5 and back

MySQL 5.5 and back

To locate which MyISAM tables have a FULLTEXT index run this query:

select tbl.table_schema,tbl.table_name from ( select table_schema,table_name from information_schema.tables where engine='MyISAM' and table_schema NOT IN ('information_schema','mysql') ) tbl INNER JOIN ( select table_schema,table_name from information_schema.statistics where index_type='FULLTEXT' ) ndx USING (table_schema,table_name);

Whatever comes out of this query cannot be converted to InnoDB until you upgrade to MySQL 5.6.

#OPTIMIZE TABLE

OPTIMIZE TABLE

##MyISAM

MyISAM

The MyISAM table is shrunk
ANALYZE TABLE runs index statistics on all indexes

##InnoDB

InnoDB

ANALYZE TABLE is totally useless because index stats are always being recollected
With innodb_file_per_table disabled, ibdata1 will grow bigger
With innodb_file_per_table enabled, tablespace (.ibd) file is shrunk

Here are some gotchas

#Memory Usage

##MyISAM

only caches index pages.
shared keycache (sized by key_buffer_size).
You can also set up dedicated keycache, one or more tables per cache table.

##InnoDB

caches data pages and index pages.
one buffer pool and one size before MySQL 5.5
1 or more buffer pools starting with MySQL 5.5

Here are some queries I wrote and posted earlier on how to choose a proper size for the MyISAM Key Cache and InnoDB Buffer Pool.

#FULLTEXT Indexes

##MyISAM

Supports FULLTEXT indexes

##InnoDB

Starting with MySQL 5.6, yes, but still in beta (UPDATE: MySQL 5.6 exists and has FULLTEXT indexes. If you are using FULLTEXT indexing in MySQL 5.6, make sure you are using the InnoDB-specific FULLTEXT options)
Before MySQL 5.6, This means you cannot convert MyISAM to InnoDB.

##MySQL 5.5 and back

To locate which MyISAM tables have a FULLTEXT index run this query:

select tbl.table_schema,tbl.table_name from ( select table_schema,table_name from information_schema.tables where engine='MyISAM' and table_schema NOT IN ('information_schema','mysql') ) tbl INNER JOIN ( select table_schema,table_name from information_schema.statistics where index_type='FULLTEXT' ) ndx USING (table_schema,table_name);

Whatever comes out of this query cannot be converted to InnoDB until you upgrade to MySQL 5.6.

#OPTIMIZE TABLE

##MyISAM

The MyISAM table is shrunk
ANALYZE TABLE runs index statistics on all indexes

##InnoDB

ANALYZE TABLE is totally useless because index stats are always being recollected
With innodb_file_per_table disabled, ibdata1 will grow bigger
With innodb_file_per_table enabled, tablespace (.ibd) file is shrunk

Here are some gotchas

Memory Usage

MyISAM

only caches index pages.
shared keycache (sized by key_buffer_size).
You can also set up dedicated keycache, one or more tables per cache table.

InnoDB

caches data pages and index pages.
one buffer pool and one size before MySQL 5.5
1 or more buffer pools starting with MySQL 5.5

Here are some queries I wrote and posted earlier on how to choose a proper size for the MyISAM Key Cache and InnoDB Buffer Pool.

FULLTEXT Indexes

MyISAM

Supports FULLTEXT indexes

InnoDB

Starting with MySQL 5.6, yes, but still in beta (UPDATE: MySQL 5.6 exists and has FULLTEXT indexes. If you are using FULLTEXT indexing in MySQL 5.6, make sure you are using the InnoDB-specific FULLTEXT options)
Before MySQL 5.6, This means you cannot convert MyISAM to InnoDB.

MySQL 5.5 and back

To locate which MyISAM tables have a FULLTEXT index run this query:

select tbl.table_schema,tbl.table_name from ( select table_schema,table_name from information_schema.tables where engine='MyISAM' and table_schema NOT IN ('information_schema','mysql') ) tbl INNER JOIN ( select table_schema,table_name from information_schema.statistics where index_type='FULLTEXT' ) ndx USING (table_schema,table_name);

Whatever comes out of this query cannot be converted to InnoDB until you upgrade to MySQL 5.6.

OPTIMIZE TABLE

MyISAM

The MyISAM table is shrunk
ANALYZE TABLE runs index statistics on all indexes

InnoDB

ANALYZE TABLE is totally useless because index stats are always being recollected
With innodb_file_per_table disabled, ibdata1 will grow bigger
With innodb_file_per_table enabled, tablespace (.ibd) file is shrunk

replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/

Source Link

edited Apr 13, 2017 at 12:42

Community Bot

Here are some gotchas

#Memory Usage

##MyISAM

only caches index pages.
shared keycache (sized by key_buffer_size).
You can also set up dedicated keycache, one or more tables per cache table.

##InnoDB

caches data pages and index pages.
one buffer pool and one size before MySQL 5.5
1 or more buffer pools starting with MySQL 5.5

Here are some queries I wrote and posted earlier on how to choose a proper size for the MyISAM Key Cache and InnoDB Buffer Pool Here are some queries I wrote and posted earlier on how to choose a proper size for the MyISAM Key Cache and InnoDB Buffer Pool.

#FULLTEXT Indexes

##MyISAM

Supports FULLTEXT indexes

##InnoDB

Starting with MySQL 5.6, yes, but still in betabut still in beta (UPDATE: MySQL 5.6 exists and has FULLTEXT indexes. If you are using FULLTEXT indexing in MySQL 5.6, make sure you are using the InnoDB-specific FULLTEXT options)
Before MySQL 5.6, This means you cannot convert MyISAM to InnoDB.

##MySQL 5.5 and back

To locate which MyISAM tables have a FULLTEXT index run this query:

select tbl.table_schema,tbl.table_name from ( select table_schema,table_name from information_schema.tables where engine='MyISAM' and table_schema NOT IN ('information_schema','mysql') ) tbl INNER JOIN ( select table_schema,table_name from information_schema.statistics where index_type='FULLTEXT' ) ndx USING (table_schema,table_name);

Whatever comes out of this query cannot be converted to InnoDB until you upgrade to MySQL 5.6.

#OPTIMIZE TABLE

##MyISAM

The MyISAM table is shrunk
ANALYZE TABLE runs index statistics on all indexes

##InnoDB

ANALYZE TABLE is totally useless because index stats are always being recollected ANALYZE TABLE is totally useless because index stats are always being recollected
With innodb_file_per_table disabled, ibdata1 will grow bigger
With innodb_file_per_table enabled, tablespace (.ibd) file is shrunk

Here are some gotchas

#Memory Usage

##MyISAM

only caches index pages.
shared keycache (sized by key_buffer_size).
You can also set up dedicated keycache, one or more tables per cache table.

##InnoDB

caches data pages and index pages.
one buffer pool and one size before MySQL 5.5
1 or more buffer pools starting with MySQL 5.5

Here are some queries I wrote and posted earlier on how to choose a proper size for the MyISAM Key Cache and InnoDB Buffer Pool.

#FULLTEXT Indexes

##MyISAM

Supports FULLTEXT indexes

##InnoDB

Starting with MySQL 5.6, yes, but still in beta (UPDATE: MySQL 5.6 exists and has FULLTEXT indexes. If you are using FULLTEXT indexing in MySQL 5.6, make sure you are using the InnoDB-specific FULLTEXT options)
Before MySQL 5.6, This means you cannot convert MyISAM to InnoDB.

##MySQL 5.5 and back

To locate which MyISAM tables have a FULLTEXT index run this query:

select tbl.table_schema,tbl.table_name from ( select table_schema,table_name from information_schema.tables where engine='MyISAM' and table_schema NOT IN ('information_schema','mysql') ) tbl INNER JOIN ( select table_schema,table_name from information_schema.statistics where index_type='FULLTEXT' ) ndx USING (table_schema,table_name);

Whatever comes out of this query cannot be converted to InnoDB until you upgrade to MySQL 5.6.

#OPTIMIZE TABLE

##MyISAM

The MyISAM table is shrunk
ANALYZE TABLE runs index statistics on all indexes

##InnoDB

ANALYZE TABLE is totally useless because index stats are always being recollected
With innodb_file_per_table disabled, ibdata1 will grow bigger
With innodb_file_per_table enabled, tablespace (.ibd) file is shrunk

Here are some gotchas

#Memory Usage

##MyISAM

only caches index pages.
shared keycache (sized by key_buffer_size).
You can also set up dedicated keycache, one or more tables per cache table.

##InnoDB

caches data pages and index pages.
one buffer pool and one size before MySQL 5.5
1 or more buffer pools starting with MySQL 5.5

Here are some queries I wrote and posted earlier on how to choose a proper size for the MyISAM Key Cache and InnoDB Buffer Pool.

#FULLTEXT Indexes

##MyISAM

Supports FULLTEXT indexes

##InnoDB

Starting with MySQL 5.6, yes, but still in beta (UPDATE: MySQL 5.6 exists and has FULLTEXT indexes. If you are using FULLTEXT indexing in MySQL 5.6, make sure you are using the InnoDB-specific FULLTEXT options)
Before MySQL 5.6, This means you cannot convert MyISAM to InnoDB.

##MySQL 5.5 and back

To locate which MyISAM tables have a FULLTEXT index run this query:

select tbl.table_schema,tbl.table_name from ( select table_schema,table_name from information_schema.tables where engine='MyISAM' and table_schema NOT IN ('information_schema','mysql') ) tbl INNER JOIN ( select table_schema,table_name from information_schema.statistics where index_type='FULLTEXT' ) ndx USING (table_schema,table_name);

Whatever comes out of this query cannot be converted to InnoDB until you upgrade to MySQL 5.6.

#OPTIMIZE TABLE

##MyISAM

The MyISAM table is shrunk
ANALYZE TABLE runs index statistics on all indexes

##InnoDB

ANALYZE TABLE is totally useless because index stats are always being recollected
With innodb_file_per_table disabled, ibdata1 will grow bigger
With innodb_file_per_table enabled, tablespace (.ibd) file is shrunk

added 515 characters in body

Source Link

edited Sep 23, 2014 at 19:50

RolandoMySQLDBA

185.6k
34
327
543

Here are some gotchas

#Memory Usage

MyISAM##MyISAM

only caches index pages.
shared keycache (sized by key_buffer_size).
You can also set up dedicated keycache, one or more tables per cache table.

InnoDB##InnoDB

caches data pages and index pages.
one buffer pool and one size before MySQL 5.5
1 or more buffer pools starting with MySQL 5.5

Here are some queries I wrote and posted earlier on how to choose a proper size for the MyISAM Key Cache and InnoDB Buffer Pool.

#FULLTEXT Indexes

MyISAM##MyISAM

Supports FULLTEXT indexes

InnoDB##InnoDB

Before MySQL 5.6, no

Starting with MySQL 5.6, yes, but still in beta (UPDATE: MySQL 5.6 exists and has FULLTEXT indexes. If you are using FULLTEXT indexing in MySQL 5.6, make sure you are using the InnoDB-specific FULLTEXT options)
Before MySQL 5.6, This means you cannot convert MyISAM to InnoDB.

This means you cannot convert MyISAM to InnoDB##MySQL 5.5 and back

To locate which MyISAM tables have a FULLTEXT index run this query:

select tbl.table_schema,tbl.table_name from ( select table_schema,table_name from information_schema.tables where engine='MyISAM' and table_schema NOT IN ('information_schema','mysql') ) tbl INNER JOIN ( select table_schema,table_name from information_schema.statistics where index_type='FULLTEXT' ) ndx USING (table_schema,table_name);

Whatever comes out of this query cannot be converted to InnoDB until you upgrade to MySQL 5.6 goes GPL.

#OPTIMIZE TABLE

MyISAM##MyISAM

The MyISAM table is shrunk
ANALYZE TABLE runs index statistics on all indexes

InnoDB##InnoDB

ANALYZE TABLE is totally useless because index stats are always being recollected
With innodb_file_per_tableinnodb_file_per_table disabled, ibdata1 will grow bigger
With innodb_file_per_tableinnodb_file_per_table enabled, tablespace (.ibd file) file is shrunk

Here are some gotchas

#Memory Usage

MyISAM

only caches index pages.
shared keycache (sized by key_buffer_size).
You can also set up dedicated keycache, one or more tables per cache table.

InnoDB

caches data pages and index pages.
one buffer pool and one size before MySQL 5.5
1 or more buffer pools starting with MySQL 5.5

Here are some queries I wrote and posted earlier on how to choose a proper size for the MyISAM Key Cache and InnoDB Buffer Pool.

#FULLTEXT Indexes

MyISAM

Supports FULLTEXT indexes

InnoDB

Before MySQL 5.6, no

Starting with MySQL 5.6, yes, but still in beta

This means you cannot convert MyISAM to InnoDB. To locate which MyISAM tables have a FULLTEXT index run this query:

select tbl.table_schema,tbl.table_name from ( select table_schema,table_name from information_schema.tables where engine='MyISAM' and table_schema NOT IN ('information_schema','mysql') ) tbl INNER JOIN ( select table_schema,table_name from information_schema.statistics where index_type='FULLTEXT' ) ndx USING (table_schema,table_name);

Whatever comes out of this query cannot be converted to InnoDB until MySQL 5.6 goes GPL

#OPTIMIZE TABLE

MyISAM

The MyISAM table is shrunk
ANALYZE TABLE runs index statistics on all indexes

InnoDB

ANALYZE TABLE is totally useless because index stats are always being recollected
With innodb_file_per_table disabled, ibdata1 will grow bigger
With innodb_file_per_table enabled, tablespace (.ibd file) is shrunk

Here are some gotchas

#Memory Usage

##MyISAM

only caches index pages.
shared keycache (sized by key_buffer_size).
You can also set up dedicated keycache, one or more tables per cache table.

##InnoDB

caches data pages and index pages.
one buffer pool and one size before MySQL 5.5
1 or more buffer pools starting with MySQL 5.5

Here are some queries I wrote and posted earlier on how to choose a proper size for the MyISAM Key Cache and InnoDB Buffer Pool.

#FULLTEXT Indexes

##MyISAM

Supports FULLTEXT indexes

##InnoDB

Starting with MySQL 5.6, yes, but still in beta (UPDATE: MySQL 5.6 exists and has FULLTEXT indexes. If you are using FULLTEXT indexing in MySQL 5.6, make sure you are using the InnoDB-specific FULLTEXT options)
Before MySQL 5.6, This means you cannot convert MyISAM to InnoDB.

##MySQL 5.5 and back

To locate which MyISAM tables have a FULLTEXT index run this query:

select tbl.table_schema,tbl.table_name from ( select table_schema,table_name from information_schema.tables where engine='MyISAM' and table_schema NOT IN ('information_schema','mysql') ) tbl INNER JOIN ( select table_schema,table_name from information_schema.statistics where index_type='FULLTEXT' ) ndx USING (table_schema,table_name);

Whatever comes out of this query cannot be converted to InnoDB until you upgrade to MySQL 5.6.

#OPTIMIZE TABLE

##MyISAM