Is your MySQL Database using INNODB or MyISAM?

As the title says. Is your Database using INNODB Or MyISAM tables? And why do you use the tables you do? Do you know? Do you know the difference between the two?

 

I do not know, nor do I know the differences. First time I've read the terms.

 

Our tables are MyISAM, but not because of any conscious choice I made. I think it is just the default. I've seen the two types mentioned, usually because of some problem that comes up because of a mix of the two in a database, but I really don't know the difference. I'd love to hear what you have to say about them.

 

Once there are a few more answers, I'll post the differences and make a recommendation on which on you should be using.

phpMyAdmin shows you what type of table you're using in the interim.

 

all my databases use MyISAM tables simply because it's the default, but I confess I don't know the difference. Please enlighten us!

 

Mine are MyISAM as well, as that is the default, apparently. I honestly have no clue what the difference is.

I look forward to your information.

 

I think I recall reading that there are problems with searching if you run INNODB, but that INNOBD is faster and suggested for larger forums? Off to search this out cuz I'm impatient at times....

 

Functionally, MyISAM is an indexed database but it is not relational. By this, I mean you cannot define relationships within MyISAM.

With INNODB you can define relationships so that when you create a new user record, it can create additional records in other tables if needed. Or when you create a new thread, the database verifies that the User ID is correct and exists in the database.

That is the base difference. There are more though.

My tables are INNODB except for some special cases as I will get to in a minute. There are specific reasons why. The first has to do with what is called locking.

Locking
MyISAM and INNODB deal with locking in different ways. Besides being relational INNODB tables are also transactional. What tht means is that different users can update records at the same time without worrying about data collision.

When you update or create a record on a MyISAM table, MySQL will lock the table and prevent anyone else from writing to it. So if two people post a message at the exact same millisecond, one will have to wait until the other is finished. On large forums this causes problems and can cause data loss.

When you update or create a record on an INNODB table, it locks the row that you are working with. This means that others can update or create additional rows at the same time. The result is faster performance for the end user and the application.

Crash Recovery
We've all had the dreaded MySQL 145 error. This signifies that your MyISAM table has crashed and needs to be repaired. This has to do with how the files are handled and your server handles them. With a MyISAM table, MySQL will make bulk writes to the disk when its buffers are full. Basically MyISAM doesn't have any kind of crash recovery built into it. When you repair a table, it checks every record and makes sure the indexes in the index file matches. If they do not, than it rewrites the index file. This can result in data loss because a record without its appropriate indexes is inaccessible.

I liken INNODB to a journaling file system. What is does is it writes each transaction to disk when it is completed. This is more reliable in that if the server fails (i.e. complete power failure) you don't lose the buffers. This makes crash recovery a lot easier to deal with. Also INNODB is designed to be self-repairing and constantly checks the integrety of its filespace.

Storage
MyISAM stores each table in a series of three files. There is a file for the table structure, one for the data and one for the indexes. The plus side is that MyISAM tables can be larger, up to 256 terabytes. That is one of the only benefits though as MyISAM doesn't support clustered indexes or transactions.

InnoDB offers the use of compressed file storage so your tables take up less space on the drive. However InnoDB is limited to tables of 64 Terabytes or smaller. InnoDB also supports pages file support and clustered indexing for faster lookups and queries.

Indexing
This is where the main differences are that would affect most people today. MyISAM supports two types of indexing that InnoDB does not. Both types would be beneficial for today's community owners. The first is Geospatial Indexing. That is latitude and longitude lookups. This is probably not well used at the moment but can be useful for geo-targetting advertising or mapping. The second is the big one and that is Full Text indexing. This is what people use for searching a lot of times.

Unfortunately, InnoDB does not support either of these at this time. It does support Clustered Indexing though. This allows the database to store similar records together. This should allow for faster retrieval in most cases.

Summary
Those are the basic differences between the two. On Windows Machines, InnoDB is the default database storage type. In order versions of the Linux version, MyISAM is the default. Some systems set InnoDB to be the default. There are other differences but they aren't as significant. Another thing about InnoDB is that you can download a plugin for MySQL at the InnoDB website that gives more features and better performance. It just overlays the InnoDB engine in MySQL.

I use InnoDB personally because it gives better performance and allows for better programming practices. That and I absolutely love Oracle databases and InnoDB has been owned by Oracle for five years. Unfortunately in vBulletin 3.X, you have to set the thread and post tables to use MyISAM for searching purposes. Luckily this will be changed with the new search in vBulletin 4.0.

 

When I go into phpMyAdmin there is a button titled "information_schema" and it brings up things like "INNODB_CMP" so I'll assume INNODB is what the database is using (hostgator).


Nice to hear 4.0 search might be better.

 

Lol, another great article of a post, Wayne.

 

This is probably a stupid question, Wayne Luke, but can you have different table types in the same database? Is there an ideal set-up for vB?

 

Yes you can.

 

As Bendo said, "yeah, you can have multiple table types in the same database".

Speaking for vBulletin (not sure how other systems handle it), there are 4 tables that shouldn't be InnoDB. When you install vBulletin it will make those four tables have the appropriate table type. The rest will use the database default. The four tables in question are session, cpsession, post and thread. The first two should be memory or heap tables and the latter two should be MyISAM.

 

Well, I'm not about to attempt changing my vB table types without reading up on them a lot more, but if I wanted to do that would it require any finagling with the vB script? I remember you're supposed to set your default table type in the config file; would changing that be enough to handle a change to mixed table types?

 

You don't set your table type in the config.php you set your database type. vBulletin currently supports MySQL and MySQLi. You don't need to make any changes to vBulletin's code if you change table types.

 

I see. Well, as I said I'm not going to try it until I understand it better, but you've given me the impetus to read up on it some more. Thank you

 

...why should your thread table by MyISAM?

In SMF, phpBB, and MyBB, at least, thread tables are among those you most want to be InnoDB. I know vB is stricter about view counter updates but...

My general recommendation is: transient data (sessions, flood control in SMF) goes MEMORY/HEAP, mass inserted data (private messages and posts) goes to MyISAM (with concurrent_insert=2), everything else to InnoDB (with a nice buffer pool and preferably large pages).

 

Fulltext searching.

Users have the option to search the post text or thread title alone. Posts can have different titles than threads.

 

...vB still uses fulltext searching? O_O

*twitch* Alright then.