5 replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/
source | link

Here are some gotchas

Memory Usage

MyISAM

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 PoolHere 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

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

Here are some gotchas

Memory Usage

MyISAM

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

Here are some gotchas

Memory Usage

MyISAM

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

4 added 515 characters in body
source | link

Here are some gotchas

Memory Usage

MyISAM

MyISAM

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.

MySQL 5.5 and back

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 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

Here are some gotchas

Memory Usage

MyISAM

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

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

Here are some gotchas

Memory Usage

MyISAM

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

3 deleted 35 characters in body
source | link

Here are some gotchas

Memory Usage

MyISAM

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

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

Here are some gotchas

Memory Usage

MyISAM

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

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

Here are some gotchas

Memory Usage

MyISAM

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

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

2 added 1 characters in body
source | link
1
source | link