Difference between MyISAM and non MyISAM tables

Hello

can anyone tell me what do you guys prefer to make your tables as, whether MyISAM or without that

and what are the benefits of MyISAM and why does a developer choose that

thanks

MyISAM is of course the default table type i'm MySQL. It's an improvement over the now-deprecated ISAM table type. It provides a simple tree structure for quick indexing on small tables, typicall fewer than 10,000 rows of data.

There are some other types of tables supported in MySQL, most of which won't provide any noticable differences.

A MERGE table is a table that only exists in memory, and will not be saved if MySQL crashes or has to be restarted. It is, as the name suggests, a merge of two (or more) tables. If you drop a MERGE table, it only drops the reference to the merge, not to the tables that are combined in it. It's not good for much!

Another table, the one which is best for huge tables, is InnoDB. It's probably the best RDBMS out there; it's actually a whole set of drivers on it's own, and when you use it, MySQL is really just a wrapper around the InnoDB table. Slashdot and other megamammoth sites out there use it, it's fast and effecient, but the trees are pretty large, so it's not good for smaller tables.

There are a few others.. like BerkeleyDB, which isn't anything special and doesn't provide any major performance increase.

The major difference between the various table types are the data tree structures and how they're handled within the RDBMS.

In the end, you'll probably find yourself sticking to MyISAM unless you're on a very large-scale project, in which cas eyou probably woudln't use MySQL anyway =D

Is it ok to add InnoDB later once the table becomes huge in the future?

Thanks,
John

I'm not sure what the difference is between old-school ISAM and MyISAM (neither was my DBMS professor).



ISAM guarantees that the next record sorted on the clustered index is adjacent on the disk. That's its main advantage: when we consider the now galactic time gap between memory access and hard disk fetches (to say nothing of the gap between memory access time and processor speed), ISAM becomes ideal for tables with vastly more queries than updates.



ISAM's weakness is that it doesn't handle updates well. In fact, it doesn't handle them at all. Instead of attaching the new row as an indexed and qualified member of the BTree, it simply references it from the appropriate leaf page. This can be fixed with a bulk load operation, but a bulk load is a form of table maintenance. If there's a difference between MyISAM and ISAM I think it would be here: it seems unfeasible to run a message board off of an ISAM system.



ISAM also offers some locking advantages over other systems.



I admit that I've quit using MyISAM all together: it doesn't have foreign key constraints. InnoDB does. InnoDB is slower than MyISAM for small tables, but the speed difference is noticeable only at around 100 queries per second. And if I’m getting 100 queries per second I usually start thinking about XML Caches anyways.



MySQL is still at a very restrictive and simplistic stage in its evolution, but it's still the best. Postgres has more SQL support, but it's slower, less stable, has a sub-standard JDBC driver, and is un-fun to maintain. SQL Server, Oracle, and DB2 are too expensive, but if you can get a trial copy (of DB2), or an Academic copy (of SQL Server), they're good databases for learning DBA tricks.