I'm very interested in this answer, too. Hopefully some light will be shed on this thread.

And if what you describe is true, depending on the write frequency of a table, the row-level locking may be faster because additional updates are not waiting for the entire table to unlock. Sounds like reads would always be a tad slower? I don't know of course.

You should probably weigh in the database backup/copy advantages of InnoDB we talked about previously. Those could mean a lot to you.

Isn't the table type something you set per table as you create them? Does the installation process for vBulletin allow you to select your table type? I know apps such as phpBB and Wordpress do not allow you to choose that--so you are going to get MyISAM tables. I suppose their is a process to convert a table's type? I wonder what other gotchas there may be.

Here is some information about this: http://dev.mysql.com/doc/refman/5.0/en/table-locking.html

Thanks for the info and the link. The page at that link sells me on sticking with MyISAM I think.

*bump* Very not long after my last post, DaniWeb moved to a dual server setup. And now, roughly one year later, it looks like we've been forced into going the InnoDB route. Surfing DaniWeb is speedy but posting has been hit or miss lately. Not sure if this is the reason but it's worth a shot I guess.

Hello

I am fighting a Mysql server since weeks to make it run ok, after a few days it gets stuck no matter what I do.

I am using some large configuration in cnf as the site suppose to handle some load.

Anyone can help understnd the issue there?
Do you have a goof cnf to test out?

I optimized my queries to the limits, at least as much as I could imagine, so I really have no ideea what to do next.

Thanks
Cris

*bump* Very not long after my last post, DaniWeb moved to a dual server setup. And now, roughly one year later, it looks like we've been forced into going the InnoDB route. Surfing DaniWeb is speedy but posting has been hit or miss lately. Not sure if this is the reason but it's worth a shot I guess.

What caused the force?

From what I have been able to gather over the last few days, InnoDB is more robust, yet more resource intensive.

This link is a bit dated, but shows what each [MYSQL storage engine] does or does not have: http://dev.mysql.com/tech-resources/articles/storage-engine/part_3.html

And, finally, 5.0 storage engine reading: http://dev.mysql.com/doc/refman/5.0/en/storage-engines.html

For small projects that do not have a need for transactions, I am hard pressed to see a reason to use InnoDB. For large-scale or ACID compliance, InnoDB seems the proper choice.

There is also how each stores their database, max table sizes, and so on.

For my projects, I will be using InnoDB. Mainly for the foreign key constraints. I am not overly concerned with resource usage.

Hey, I just thought I'd chime in with my 25 cents... (hey, that's inflation for ya)

I recently had an InnoDB database crash. I was using a web application that allowed me to store notes, and I had input a lot of notes into this app which was self hosted. Out of the blue, I could no longer login because the application was not able to locate the database. Upon contacting the host (TextDrive Sucks....) they said it was because the database was of InnoDB format. That also meant that they could not restore the DB because InnoDB tables only backed up structure not data.

It's really wack. But I highly advise you to stick with MyISAM, I've never had a problem with it. Also, backup your data often, I can't stress that enough! I'll live, obviously, without my notes. But who knows? I may have had the next trillion-dollar idea stored up, and now it's for the birds! (I'm highly exaggerating now.)

Good to know the differences though, I suppose. This forum seems speedy enough to me.

Hello, all. I just thought I would chime in a few things about this that I have realized the hard way about this question, and see if anyone can help me with my own problem.

MyIsam
First, if you want to search through large samples of text efficiently, you want a FULLTEXT index - this allows people to search things like webpages in a page-ranked "google-style", and it's been really helpful. Only MyISAM can do this.

The fulltext index creates a numeric index of all the words, ranks them by frequency of the word within each document, the length of the word, and the length of the document. The engine uses a formula that combines all of these factors into one value that ranks the results when you search for the terms.

I'm sure plenty of folks in here are familiar with trying to search through a bulletin board or a website for something specific and seeing results that never seem to make any sense - these results are simply counting each specific instance of the words in a document and spitting out the results. It makes the process of finding anything in a large collection extremely annoying.

Innodb
Second, if you want to enforce referential integrity between tables, you must use Innodb. I just found this out today, and I am pretty frustrated, because I need this feature for a system I have already created and populated. If you aren't familiar with referential integrity, (apologies if this is over-simplified) it's really important to enforcing business rules when you are creating a db. If I want to link the primary key from one table to a foreign key in another table and not allow values in the child table that don't exist in the parent already, I have to use Innodb.

A good example of this would be to say "I only want to allow sales offers to be linked to pre-approved customers" - and you have a table of pre-approved customers and a separate table of offers - you would have to create a bunch of application code to enforce this, instead of doing it in the db (both can be done, of course... but that's another discussion). If it was done in the db, it would never allow the offer to be created for a customer who didn't exist in the customer table already.

I found this out the hard way with a system I created from scratch using MyISAM tables (because I needed FULLTEXT). The thing that bugs me the most about this mess is that MySQL created my foreign key constraints and told me everything was cool.
I assumed everything would work if it did that.

I'm sure many of the folks in here have worked with keys instead of foreign key constraints to link tables (I am used to dealing with Oracle products). Can anyone show me an example that would be equivalent? So, what to do now? Create indexes? What's the best way to do something like this:

ALTER TABLE votes ADD CONSTRAINT FK_votes_REFS_citation 
    FOREIGN KEY (master_id) REFERENCES citation (master_id);

... with keys? Also, I only need to have 3 out of about 20 tables to be fulltext-searchable... Should I just convert the other 17 to Innodb? Will tables using different engines link up efficiently?

Again, I apologize if this is over-simplified or has been answered already. Thanks in advance!

Sadly, no. You cannot link Inno and MyISAM and maintain foreign key constraints. I found this one out the hard way as well and is causing me a slight headache.

I did convert the MyISAM to InnoDB without issue, but I am unsure if other functionality was broken in the process (constructing a wrapper app for an already existing app).

I do not really need big time constraints. But, I enjoy my database handling as much logic as it can (unique, foreign keys, etc) and letting the app play dumb.

Sadly, no. You cannot link Inno and MyISAM and maintain foreign key constraints. I found this one out the hard way as well and is causing me a slight headache.

Can I maintain the constraints between the innodb tables only, and just hope for the best with the three tables requiring fulltext? I did convert the MyISAM to InnoDB without issue, but I am unsure if other functionality was broken in the process (constructing a wrapper app for an already existing app).

So, no lost data or anything like that? Is there a way to create indexes that do the same thing in MyISAM? I do not really need big time constraints. But, I enjoy my database handling as much logic as it can (unique, foreign keys, etc) and letting the app play dumb.

I agree. It keeps everyone honest. Thanks.

I used InnoDB for a while and just recently finished switching everything back to MyISAM. It was really hard to reliably back up and if you ever decide to set up replication forget about it.
Weighing all the pros and cons though I would say stick with MyISAM unless you absolutely need the features that InnoDB provides and you can't get away with just building them into your application.

Hope that helps :-/

The way we typically back up MySQL at work is to run MySQL with replication between two systems, and once a day we bring down the mysql instance on the passive box and perform a file-level backup.

It gets really touchy if the two somehow get out of sync (as often did happen with MySQL's various untraceable bugs).
In that situation, Linux LVM snapshots became a godsend--it was very little trouble to bring down the primary database for 5 minutes, create a snapshot volume of the database's filesystem, get the primary back up and do a concurrent file copy to the passive system.

...if you want to enforce referential integrity between tables, you must use Innodb.

Hi Anjama and others,

I'm in the initial stages of building a MySQL database for my employer using the MyISAM engine. I intend to limit database access to stored procedures and triggers which will enforce referential integrity. That seems to be a fairly obvious, but time-consuming and complicated way to implement it to me. It seems like I'll be reinventing the wheel a bit and no doubt it would be a nightmare to implement for anyone trying to convert a large, populated DB from InnoDB. Does anyone know if there is there a better way to do this?

Thanks,

Chris Fry
Canberra, Australia