Solved

MySQL Engines

Posted on 2015-02-07
3
147 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 110

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 35

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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

617 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