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

There are maybe several hundred writes a day, versus the tens of thousands of reads. My databases are located on an SSD drive.

Based on this statement, let's play with some numbers. Lets's say there are 500 writes a day and 20,000 reads a day. That computes out to the following

• 97.56% reads per day
• 2.44% writes per day
• 40 reads / write

As much as I love InnoDB, I would have to choose MyISAM in this case for several reasons

REASON #1

You only have 150MiB of MyISAM tables (70MiB Data, 80 MiB Indexes)

REASON #2

InnoDB indexes tend to get very bloaty because Secondary Indexes have keys into the Clustered Index. This always results in a double index lookup. This can be overlooked with large, write-heavy datasets.

REASON #3

InnoDB tablespaces tend to get very bloaty because of MVCC being created and discarded without an automatic reclaiming of disk space:

• With innodb_file_per_table disabled, the system tablespace file ibdata1 would steadily grow with 0% chance of reclaiming of disk space. You would have to convert InnoDB storage engine to use innodb_file_per_table.
• With innodb_file_per_table enabled, you could go to the individual InnoDB and run of the the following to physically shrink the InnoDB table mydb.mytable:
  • OPTIMIZE TABLE mydb.mytable;
  • ALTER TABLE mydb.mytable ENGINE=InnoDB;

All this can be avoid with MyISAM

REASON #4

InnoDB protects individual rows by performing MVCC for transaction control. The overhead generated for reads in a day would probably be greater than 150 MiB.

I can probably name 2 or 3 more reasons, but let's cut to the chase: Is there anything that can improve performance for MyISAM in your case? Why, yes there is.

Your said the following

The table is about 260k rows in size, with 28 fields which for the most part is varchars and ints

If you have many varchars, there is something you can do to increase read/write performance. For any MyISAM table mydb.mytable: run this command:

ALTER TABLE mydb.mytable ROW_FORMAT=Fixed;

What will this do, this will treat all VARCHARs as CHARs. Every row will be the exact same length. This will increase disk space 80%-100%. In your case, let's assume it doubles your 150 MiB MyISAM table to 300 MiB. Where is the benefit? Your MyISAM table can now be read/written anywhere from 20% - 30% faster without changing anything else I learned that from pages 72,73 from MySQL Database Design and Tuning.

I have written about this in the past:

• Which is faster, InnoDB or MyISAM? (May 03, 2012)
• Which mysql storage engine to choose? (May 02, 2012)
• Best of MyISAM and InnoDB (Sep 20, 2011)
• Which DBMS is good for super-fast reads and a simple data structure? (Aug 12, 2011)
• What is the performance impact of using CHAR vs VARCHAR on a fixed-size field? (May 10, 2011)