Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 157
  • Last Modified:

MySQL Engines

Hi Experts,

If I want a table engine for performance, without FK and transaction support, if MyISAM the way to go?

However, if I need all of the above, I am assuming InnoDB, but is is slower.

Is my understanding correct?

Thank you
0
APD Toronto
Asked:
APD Toronto
1 Solution
 
Ray PaseurCommented:
There are so many other considerations that it's probably not helpful to try to give an answer, but here are a few guidelines.  MyISAM is generally thought to be faster, but less capable than InnoDB.  However your data structure and your queries will have more influence on performance than your choice of engine.  Poorly written queries or missing indexes will make any database look bad.

Use ENGINE=MEMORY instead of MyISAM or InnoDB and you'll get markedly better performance, but the data will not persist.  This sort of approach is useful in a "down-select" strategy that starts with a huge table and isolates part of it for more intensive query / analysis.  

If your data is correctly indexed, the differences will be negligible.  And any database can be tuned for good performance.
http://www.experts-exchange.com/Database/MySQL/A_1250-3-Ways-to-Speed-Up-MySQL.html

Different tables can use different storage engines, so you can choose the engine that makes most sense for the application.

Using either MyISAM or InnoDB, if you're inserting into a table with a UNIQUE constraint, you will begin to see slowdowns around 500,000 rows.

Does any of that help answer the question?
0
 
APD TorontoAuthor Commented:
Thanks
0
 
gr8gonzoConsultantCommented:
I know this question's already been answered, but a lot of the MyISAM-is-faster-than-InnoDB thinking is incorrect. It's sort of like when you have "common sense" ideas that end up being being portrayed as fact and then people just start accepting it over time, and it becomes hard to shake that idea.

InnoDB will almost always be faster in every situation compared to MyISAM, -especially- in situations where you have a table that has a mix of reading and writing going on all the time. In fact, the only way that I can think of where MyISAM is faster is when it comes to some occasional index/key maintenance, but even then, it's not that much different from InnoDB in the real world.

The rest of what Ray said is still spot-on. Most times, it's less about the database engine and more about the architecture and strategy.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now