Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to optimize our database tables. I have some sql skills, but complete newbie at db administration. Our db operations are pretty slow, and I have been assigned to review the storage engines and create indexes. So, here's the thing... Most of our tables are MyISAM. And most of the tables are used for both read and write, constantly. The tables are also very related, and most selects pull from at least 4 or 5 tables. After hours spent reading recent blog posts and stack exchange answers - looks like InnoDB would be the way to go.

But, our db is hosted, and after discussing briefly with our host about switching to InnoDB, he came up with the following complaints

  • InnoDB is slower at most things
  • InnoDB is hard to backup
  • InnoDB only shines when you have complex joins on tables with udpates and inserts.

So here is my question - are his complaints valid? Should I only select a chosen few tables to convert to InnoDB, the ones with really complex joins?

Thanks

share|improve this question
1  
1. UDPATE is my favourite SQL command, I use it every now and then :) 2. What does hard mean? Backing up InnoDB has its tools AFAIK. –  dezso May 30 '12 at 8:50
add comment

1 Answer 1

up vote 6 down vote accepted

Have a look at this link What are the main differences between InnoDB and MyISAM.?

After looking into this you will somewhat able to decide what to do.

From your question prospective :

1.InnoDB is slower at most things:

It is not true well designed InnoDB tables performs very well i have a 1TB of data with InnoDB table and is performing well

the reason for poor performance may be

improper use of indexes

fragmentation in tables

improper setting of innodb variables like innodb_buffer_pool , log files,how the table space is configured

There may be a lot of reasons due which it doesn't perform well. Have a look how to tune InnoDB Performance

2.InnoDB is hard to backup

It is not also very hard to backup the InnoDB tables there might be time that you feel it is tough because of its table space/log files/frm files.As for a large MyISAM table you can directly copy the databases using the copy operation and it is quite fast.

due to that you might feel that backing up InnoDB is hard but it is not so.

There are proper backup techniques for InnoDB tables also, please see the following link for this Backing Up and Recovering an InnoDB Database.

3.InnoDB only shines when you have complex joins on tables with udpates and inserts.

No they can also perform well in all cases in which the MyISAM can.

share|improve this answer
    
@esther H : If you need more clarification please ask.. –  Abdul Manaf May 30 '12 at 9:18
    
these are great links, I am reading them now, thanks –  esther h May 30 '12 at 10:43
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.