Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MySQL Engines

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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
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 create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Suggested Courses

876 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