Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MySQL Engines

Posted on 2015-02-07
3
Medium Priority
?
151 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 111

Accepted Solution

by:
Ray Paseur earned 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this article, we’ll look at how to deploy ProxySQL.
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 …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

688 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