Solved

MySQL Engines

Posted on 2015-02-07
3
130 Views
Last Modified: 2015-02-09
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
Comment
Question by:APD_Toronto
3 Comments
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 40595990
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
 

Author Closing Comment

by:APD_Toronto
ID: 40597037
Thanks
0
 
LVL 34

Expert Comment

by:gr8gonzo
ID: 40598499
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now