[Max]

I've working with an app that currently uses a table for storing unique rows of data. None of the rows should ever be duplicates and the table is only 10 columns wide.

Currently the table has about 1.1 million rows and holds about 155 megs of data. At the rate rows are being added I can foresee a point where it's going to grow considerably larger, eventually 5 million rows or so.

The table is searched frequently and rows are added frequently.
There are rarely any DELETES or UPDATES on the table.

What's the "best" table type for something like this? It's a MyISAM table at the moment and I'm wondering if I should switch to InnoDB or MERGE, or some other table type?

InnoDB uses Row level locking which would help speed things up a bit from what I understand, but I'm not sure speed is the primary concern. Crash prevention/recovery is high on the list and InnoDB is supposed to be good in that respect.

MERGE tables would let me spread the rows out among several tables and the usual cautions about using MERGE tables may not matter in this application. (From what I've read, you can't do DROP TABLE, ALTER TABLE, DELETE FROM table_name without a WHERE clause, REPAIR TABLE, TRUNCATE TABLE, OPTIMIZE TABLE, or ANALYZE TABLE on any of the table that is mapped by a MERGE table that is "open"...but these aren't things that I'd normally do in this application.)

A HEAP table would be good for speed, and I suppose I could copy it to a "real" table periodically so I could recover from a crash or system hiccup. However, gobbling up 150 to 250 megs of RAM isn't something I want to do unless a HEAP table is the best solution.

Any suggestions, cautions,or comments?

[wheels]

I'm no database expert, but my approach to speeding up database operations is to look, think, and tinker.  You've probably already done looking and thinking - tinkering may be a good options.

You could make a copy of the database - or at least the large table and tinker with it in MySQL to check the performance by running queries.

Pick a common query pattern, run it on the existing data/table a few times (one sample isn't enough to really see a pattern), then begin making changes, and run the same queries.  Be sure to keep track of the changes.

Changes that may be good are adjusting the indexing, changing column types, reducing column widths, switching to InnoDB or MERGE.

Other ideas include looking for patterns in the data so you could break the table into smaller segments.  For example if it is last names - separate the data by the first letter of the last name, so all the As would be in one table, all the Bs would be in another.  Consolidation may be an option, if you are using IP addresses, you may be able to bundle the IP addresses and use a mask or CIDR system to indicate multiple addresses with a single row.  It may also be possible to archive data out, if any is expiring.  You could timestamp the row creation and last access timestamp and then clear data that hasn't been accessed for a certain amount of time, if that's is okay for the application.

[Max]

Consolidation may be an option, if you are using IP addresses, you may be able to bundle the IP addresses and use a mask or CIDR system to indicate multiple addresses with a single row. 

Unfortunately I can't use CDIR or any kind of ranges; I have to be able to search the IPs individually.

It may also be possible to archive data out, if any is expiring.  You could timestamp the row creation and last access timestamp and then clear data that hasn't been accessed for a certain amount of time, if that's is okay for the application.

This is probably something I'll do just to keep the overall size of the table down. Perhaps after 6 months or a year I can 'age' them out of the table.