Easy Tutorial
Android Tutorial Drawable Tool Android Tutorial Broadcastreceiver Shell Summary Brackets Android Tutorial Xfermode Porterduff4 Awk Work Principle Csharp Init Variable Programmer Joke 14 Sql Join Image Explain Android Tutorial Webview Error Code Es6 Reflect Proxy The Last Line Effect Programmer Joke 25 Game Programmer Awk Arrays Java Polymorphism Const Char Java Annotation C Assert C Language Pointer From Five Dimensions Verilog Compile Instruction Java Sorting Algorithm Analysis And Implementation Programming Range Awk Built In Functions Linux Tar Gz State Vs Strategy Android Tutorial Service 1 Bucket Sort Code God Annotation Appreciation Css Position Static Relative Absolute Fixed Android Tutorial Button Imagebutton Javascript Console Object Android Tutorial View Viewgroup Intro Programmer Joke 21 Nodejs Callback Func Eclipse Shortcuts Java Inner Class Intro Android Tutorial Edittext Java Protected Keyword Detailed Explanation Programmer Facial Package Summary Android Tutorial Sensor2 Android Tutorial Touchlistener Ontouchevent Php Namespace Intro Redis Intro Data Structure C Get Keycode Python Openssl Vulnerability Scanning Verilog2 Udp Logic Android Tutorial Exercise 2 Zookeeper Setup Python Docstrings Es6 Concise Tutorial Insertion Sort Best Python Ide For Developers Python Append Deepcopy Php Rounding Js Random Python Spider Intro Verilog2 Rtl Low Power Design 2 Verilog Fir Verilog Install Perl Output Chinese Warning This Program Uses Gets Which Is Unsafe Android Tutorial Handler Message Android Tutorial Sqlite 2 Zookeeper Bs Command Android Tutorial Canvas Api2 React Click Event Bind Localstorage Spec Css Nowrap Break Word Verilog2 Specify Prints Diamonds Triangles Rectangles Moible Web Front Source Android Tutorial Alarmmanager Funny Programmer Pictures Android Tutorial Json Decimal Decimals Are Converted To Binary Fractions Android Tutorial Project Src Analysis Es6 String Hdfs Cluster Html5 Canvas Zrender Cpp Header Cpp Vector Container Analysis Awk If Loop Android Tutorial Webview Cache Android Tutorial Popupwindow Verilog2 Gate Rsa Basic Idea Android Tutorial Httpurlconnection Sql Different On And Where Google Python Styleguide Verilog2 Tf Android Tutorial Intent Base Hadoop Tutorial C Volatile Keyword Npm Slow Use Cnpm Verilog Function Openresty Intro Javascript Qrcodejs Library Python Complex Intro Android Tutorial Valueanimator Verilog Process Structure Centos Mail Command Sendmail Verilog Numerical Conversion Structure Variables And Structure Types All Vim Cheatsheat Android Tutorial Baseadapter Python Variable References And Copies Android Tutorial Typeface Js Filter Lists Strlen And Sizeof Git Guide Javascript Basic Types And Reference Types Programming En Cn Javascript Number Capitalization Lua Table Length Analysis Android Tutorial Gridlayout Js Get Current Page Url Android Tutorial Fragment Demo4 Android Tutorial Listener Edittext Change Bit Operation Linux Common Command 2 Csharp Foreach Javascript Find Min Max Android Tutorial Socket1 Android Tutorial Contentprovider 2 Programmer 520 Cpp Difference Between Pointers And References Nginx Setup Intro Ssh Slow Verilog2 Real2Int Python Unboundlocalerror Android Tutorial Activity Js Timer Css Calendar Style Javascript Remove Array Item 2021 Frontend Learnpath Express 4 X Api Android Tutorial Shader Cloud Atlas Engineer Skills Es6 Setup Android Tutorial Drawable2 Find The Best Language Rogramming Languages Used In Most Popular Websites Http Vs Https Linux Shutdown Reboot Python Locals Globals Android Tutorial Radiobutton Checkbox Remainder And The Modulo Javascript Table Search Filter Programmer Joke 18 Javascript Var And Without Var Java Swing Demo Intro Android Tutorial Git Repo Create Es6 Tutorial Android Tutorial Webview Attention Verilog2 Codeguide Android Tutorial Togglebutton Switch Cpp Inline Usage Js Jquery Refresh Iframe Verilog2 Lower Power Design Android Tutorial Sqlite Intro Search Websites Recommand Javascript Image Modal Index Android Tutorial Upload File Android Tutorial Fragment Demo1 Zookeeper Node Feature Cpp Two Colon Usage Verilog2 Timing Check What Is A Virtual Method Intelligent Dev Ops Android Service Tutorial Docker Use Container Create Image Delegate Mode Android Tutorial Git Repo Operate Set Ssh Login Key Verilog Defparam Verilog2 Clock Linux Mysql Import Export Data Programmer Without Network Deconstruction Assignment Python Yield Used Analysis Android Tutorial Relativelayout Javascript Check Null Undefined Android Tutorial Activity Start Android Tutorial Notification Java String And Int Convert Php Develop Tools Python Redis Intro Android Tutorial Smsmanager Android Tutorial Fragment Demo5 Fastjson Intro Coreos Setup Pc Zookeeper Leader Quick Sort Android Tutorial Alertdialog Javascript Autocomplete Detect Empty Javascript Object C Array As Parameter Vue2 Start Coding Verilog Design Method Android Tutorial Interface Design Verilog2 Reset Php Delete An Element From An Array Android Tutorial Fragment Base The Different Of Jre And Jdk Android Tutorial Gridview Verilog Time Delay Database Implementation Svn Co Github Dir Android Tutorial Drawable3 Html Online Editor Zookeeper Tutorial Linux Shell Brackets Features Android Tutorial Ratingbar Es6 Array Programmer Joke 19 Python 5 Underline Android Tutorial Date Time 2 Shell Quick Start Ten Sorting Algorithm Es6 Class Java The Different Float Double Android Tutorial Textview Android Tutorial Socket Intro Android Tutorial Colorfilter1 Verilog2 Integrated Design Website Reward Button While Explain Programmer Alive Radix Sort Verilog2 Sdf Android Tutorial Download1 Cpp Func Setw Android Tutorial Listview Programmer Joke 15 Lets Encrypt Https Android Tutorial Xfermode Porterduff5 Google Chrome Shortcuts 10 Best Program Html Import Css Method Android Tutorial System Architecture Analysis Http X Forwarded For Programmer Joke 24 Android Tutorial Intent Pass Data Java Init Object Process Js Debugging Skills Redis External Network Android Tutorial Alphaanimation Android Tutorial Gestures Android Tutorial Vibrator Android Tutorial Tablelayout Octotree Github Repo C Void Intro Python Subprocess Basic Knowledge Summary Of Spring Cpp Virtual Functions Docker Clear Command Px Em Rem Different Runoob Tool Update Desc Zabbix Monitor Snmptrap Verilog2 Acc Python String Index Viewport Deep Understanding Android Tutorial Ansynctask Programmer Joke 20 Verilog Dds Linux Command Full Fight Es6 Module Javascript Or And And Android Tutorial Mediaplayer Css Search Bar Js Sort List Android Tutorial Sensor3 Npm Switch Repo Android Tutorial Exercise 3 Nvm Manager Node Versions Zookeeper Watcher Android Tutorial Broadcastreceiver 2 Verilog Number Android Tutorial Xml Android Tutorial Animation Php Session Login Android Tutorial Listview Update Php Different Include And Require Working Process Of The Compiler Builder Pattern Android Tutorial Bitmap1 Hadoop Skills Android Tutorial Adapter Android Tutorial Wallpapermanager Scrapy Detail Python Single Thread Multi Thread And Multi Process Verilog Loop How To Make Your Code Self Documenting Cpp Keyword Intro Mysql Different Nnodb Myisam Java Varargs Parameter 9Patch 9 Remove Black Line Window Getcomputedstyle Method Android Tutorial Shadowlayer Verilog2 Clock Switch Android Tutorial Canvas Api3 Cpp Multithread Demo Hex Dec Oct Bin Android Tutorial Scrollview Es6 Promise Android Animationdrawable Meta Verilog2 Gate Delay Java Transient Keywords Android Tutorial Camera Linux Check Port Usage Verilog2 Codestyle Jquery Check Id Is Exists Android Tutorial Telephonymanager 8 Awesome Awk Built In Variables Java Fail Ast Fail Safe Most Popular Javascript Code Specifications Zookeeper Sc 4Lw Scala Different Function Method Verilog2 Acc Sub Java Interface And Polymorphism Android Tutorial Fragment Demo3 Ios Dev Skills Android Tutorial Contentprovider Es6 Number Front End Developer Questions And Answers Ascii Quick Sort 2 Counting Sort Node Multi Version Verilog2 Slow2Fast Android Tutorial Sharedpreferences Es6 Async Tencent Open Source Android Tutorial Windowmanager 15 Php Mind Mapping Android Tutorial Toast Verilog Dividend Verilog Serial Fir Android Tutorial Http Verilog2 Udp Verilog Fsm Android Tutorial Gps Javascript Lightbox Big Data Require Skills Pycharm Import Python Package Cpp Return Array Safety Engineer Skills Hadoop Setup Pip Cn Mirror 2018 Web Developer Shortcut Key C The Structure Of The Parameter Android Tutorial Colorfilterl2 Verilog Competition Hazard Html5 Canvas Eccharts Composer Install And Usage Android Tutorial Spinner Android Tutorial Configuration Csharp Enum Verilog Case Verilog2 Delay Type Verilog Latch Verilog Timing Control Programming Language As A Pepole Verilog Testbench Verilog Statements Block Android Tutorial Socket2 Git Gui Window Mobile Testing Skills Android Tutorial Maskfilter Git Graphical Php Phpmailer 2014 Need To Learn Zookeeper Session Cpp Func Pointer Java Print The Triangle Onreadystatechange Attribute Learn Verilog Basic Syntax Android Tutorial Drawable1 Verilog Data Type Android Tutorial Sign Package Javascript Slideshow Viewport Template C Callback Function Download Js Facade Pattern 3 Chrome Skip Git Five Minutes Tutorial Android Tutorial Progressbar Cpp Static Usage Android Tutorial Soundpool Programmer Mobile Wallpaper Do Not Ignore Web Small Details C Swap Data Android Tutorial End Android Tutorial Bitmap Demo Programmer Joke 17 Cpp Std Thread Factory Pattern C Templates Detail Verilog Module Port Chrome Password Android Tutorial Colorfilter3 Verilog Tutorial Javascript Css Accordion Css Nav Bar Android Tutorial Webview Android Tutorial Exercise 5 All Vim Cheatsheat 3 Verilog2 Other Task Zookeeper Data Sync C Int2Str Verilog Fft Windows10 Java Setup Yaml Intro Programmer Joke 26 Java Composite Pattern 2 Android Tutorial Audiomanager Python Tower Verilog2 Random Filter Filterchain Filterconfig Intro Android Tutorial Decompile Apk Get Code Resources Javascript Toolbox C Static Effect Php Learning Recommend Github Tools Android Oom Android Tutorial Customer Baseadapter Js Call Apply Bind Android Tutorial Service 2 Android Tutorial Sensor1 Python Pip Install Usage Python Round Func Note Heap Sort C Constant Pointer Runoob Architecture Programmer Joke 22 Zookeeper Acl Android Tutorial Menu Android Tutorial Activity Intro Cpp Static Const Android Tutorial Xfermode Porterduff3 Verilog Process Assign Lua Table Length Hdfs Setup Android Tutorial Exercise 1 Android Tutorial Dialog Es6 Let Const Verilog2 Rtl Low Power Design 1 Java Knowledge Structure Diagram Android Tutorial Httpclient Js Get Url Param Verilog2 Setup Hold Time Python Ten Minute Introductory Tutorial Javascript Basic Reference Object Types Cpp Construct Function Initial List Weui For Weixin Web Android Tutorial Xfermode Porterduff Verilog Cic C Structures Intro Front End Standards Android Tutorial Canvas Api1 Verilog Intro Android Tutorial Autocompletetextview Inner Lambda Final Android Tutorial Webservice Bubble Sort Restful Architecture Js Filter Table Verilog2 Pli Intro Android Tutorial Download2 Android Tutorial Callback Event Handle Programmer Joke 16 Internet Organization Github Gcc Parameter Detail Verilog Pipeline Design Python Print Func B Android Tutorial Exercise 4 Closure Intro Verilog2 File These Pain Only Programmers Know C General Function C Dynamic Array Three Tier Architecture C Pointer Detail Hadoop Intro Android Tutorial Genymotion Install Cpp Enums Intro And Strongly Typed Es6 Function Verilog2 Fast2Slow Sql Join The Different Of On And Where Android Tutorial Sensor4 Android Tutorial Layoutinflater Implementation Of Merge Sort Css Percentage Calculation Vscode Tutorial Bridge Pattern2 Programmer Joke 27 Verilog Generate Android Tutorial Imageview Front End Engineers Required Skills Verilog Task Es6 Map Set Runoob Chm 15 Sorting Algorithms Visually Displayed Verilog Sync Verilog2 Udp Timing Intellij Idea Usage Android Tutorial Listview Item Scala Option Some None Android Tutorial 9 Image Python Func Decorators Verilog2 Tutorial Javascript Copy Clipboard Mysql8 Error 1410 42000 You Are Not Allowed To Create A User With Grant Programming Error Zookeeper Linux Cluster Css En Cn Break Line Android Develop Mvp Pycharm Windows Install Code Editor Mysql Stored Procedure Shell Process Substitution Android Tutorial Service 3 Verilog2 Clock Division Python Core Knowledge System Of Programming Px Pt Em Convert Table Csharp Polymorphism2 Web Image Optimization Android Tutorial Valueanimator2 Ubuntu Modify Dns Android Tutorial Listview Checkbox Chrome Extensions Recommended C Single Quotes And Double Quotes Verilog2 Logic Sumarry Bash Shortcut Javascript Check Arrayisobject Summary Of Network Html Meta Intro Verilog Condition Statement Nginx Proxy Balancing Matplotlib Tutorial View Ssh Public Key Mac Scala Chinese Show Python One And Two Star Php Get Url Suffix Programmer Joke 23 Css Search Box Android Tutorial Powermanager Mqtt Intro Android Tutorial Patheffect Python Partial Developer Love Php Web Development Android Tutorial Xfermode Porterduff2 Zookeeper Java Setup Python Understanding Dict Copy Shallow Or Deep Python Basic Datatype Free Html5 Css3 Loaders Preloaders Cpp String Array Pointer Es6 Generator Android Tutorial Listen Event Handle Scala Method Functio Different Java Abstract Interface Different Android Tutorial Android Studio Vmware Install Centos7 The Different This Super Php Remove Firsrt Array Element Embedded Engineer Require Skills Mapreduce Usage Front End Interview A Few Important Points Of Knowledge Js Filter Dropdown Android Tutorial Webview Download File Android Tutorial Bitmap2 Mapreduce Coding Android Tutorial Sdk Problem Solve Shell Read Line Db Solutions Android Tutorial File Jquery Check Element Is Hidden Android Tutorial Intro Clean Code Versus Great Code Cpp Enum Intro Java Compile With Package Html5 Canvas Clock Java Json Instro Android Tutorial Absolutelayout Cpp Const Keyword Android Tutorial Framelayout Linux Autologin Shell Verilog2 Display Open Source License Js Precision Problem And Solution Programming Intro Android Tutorial Mediarecord Reg Lookahead Lookbehind Webpack Tutorial Zookeeper Locks 25 Best Game Engine Libraries For Developers Awk User Defined Functions Js Delete Json Arr Merge Sort Android Tutorial Linearlayout Android Tutorial Fragment Demo2 Android Tutorial Listview Focus Java Math Floor Ceil Rint Round Verilog Deassign Cpp Virtual Function Android Tutorial Development Environment Build Verilog2 Level Modeling Float And Double Different A Beginners Guide To Web Development Struct Size Java Class Forname Python Print Without Newline Verilog2 Fifo C Bool True False Https Ssl Intro Javascript Window Onload Java Stringtokenizer Intro Js Sort Table Es6 Symbol Shell Sort Docker Tricks Js Redirect To Another Webpage Zepto Js Source Analysis C Matrix Exchange Android Tutorial Seekbar Javascript Get The Value Of Text Input Field Wx Xcx Repo Android Tutorial Viewflipper Cglibcode Generation Library Intro Htmlcss Make A Website Mac Os Ssh Pem Builder Pattern 2 Java Capture Group Linux Useful Command Css Style Priority Hbuilder Intro Android Tutorial Exercise 6 Html Windows10 Mysql Installer Macro Definition Verilog2 Tf Sub Android Tutorial Udp Socket Php Ajax Cross Border Javascript Promise Object Android Tutorial Viewpager Python Extends Init Js Timestamp2Date Javascript Search Auto Tip Zookeeper Znode Data Model Verilog Assign Windows Finds Port Usage Android Tutorial Contents Android Tutorial Eclipse Adt Sdk App Java Different Of String Stringbuffer Stringbuilder Getting Started With Typescript Div Scroll Javascript Modals Android App Develop Learning Python Dynamic Var Selection Sort Java Inner Class Summary Python2 Python Raw_Input And Input Java Printf Formate Demo Go Channel Intro Html5 Canvas Intro Android Tutorial Date Time 1 Android Tutorial Expandablelistview Cpp Reference Type Es6 Object Android Tutorial Http Response Header Verilog2 Low Power Facade Pattern 2 Android Tutorial Drawerlayout Verilog Expression Android Tutorial Webview Javascrip Java Transformation Problem Trinocular Operators In Lua
❮ Cpp Keyword Intro Java Varargs Parameter ❯

Six Major Differences Between MySQL Storage Engines InnoDB and MyISAM

Category Programming Technology

>

Abstract: MySQL offers multiple storage engines, each with its own advantages and disadvantages, allowing for optimal selection: MyISAM, InnoDB, MERGE, MEMORY(HEAP), BDB(BerkeleyDB), EXAMPLE, FEDERATED, ARCHIVE, CSV, BLACKHOLE.

MySQL supports several storage engines, each with its own advantages and disadvantages, allowing for optimal selection:

MyISAM, InnoDB, MERGE, MEMORY(HEAP), BDB(BerkeleyDB), EXAMPLE, FEDERATED, ARCHIVE, CSV, BLACKHOLE.

MySQL supports multiple storage engines as processors for different table types. These engines include those that handle transaction-safe tables and those that handle non-transaction-safe tables:

-

MyISAM manages non-transactional tables. It offers high-speed storage and retrieval, as well as full-text search capabilities. MyISAM is supported in all MySQL configurations and is the default storage engine unless configured otherwise.

-

The MEMORY storage engine provides "in-memory" tables. The MERGE storage engine allows grouping of identically processed MyISAM tables into a single table. Like MyISAM, the MEMORY and MERGE storage engines handle non-transactional tables and are included by default in MySQL.

Note: The MEMORY storage engine is formally identified as the HEAP engine.

-

InnoDB and BDB storage engines provide transaction-safe tables. BDB is included in the MySQL-Max binary distributions for operating systems that support it. InnoDB is also included by default in all MySQL 5.1 binary distributions, and you can configure MySQL to enable or disable either engine as preferred.

-

The EXAMPLE storage engine is a "stub" engine that does nothing. You can create tables with this engine, but no data is stored or retrieved. This engine serves as an example in the MySQL source code to demonstrate how to start writing a new storage engine. Its main interest is for developers.

-

NDB Cluster is the storage engine used by MySQL Cluster to implement distributed tables across multiple machines. It is provided in the MySQL-Max 5.1 binary distributions. This storage engine is currently supported only on Linux, Solaris, and Mac OS X. In future MySQL distributions, we plan to add support for other platforms, including Windows.

-

The ARCHIVE storage engine is used for storing large amounts of data without indexes in a very small footprint.

-

The CSV storage engine stores data in text files in comma-separated format.

-

The BLACKHOLE storage engine accepts but does not store data, and retrieval always returns an empty set.

-

The FEDERATED storage engine stores data in a remote database. In MySQL 5.1, it works only with MySQL using the MySQL C Client API. In future distributions, we plan to enable it to connect to other data sources using other drivers or client connection methods.

The Most Commonly Used Are MyISAM and InnoDB

| <br> <br> | MyISAM <br> <br> | InnoDB <br> <br> | | Structural Differences: <br> <br> | Each MyISAM is stored on disk as three files. The first file name begins with the table name and the extension indicates the file type. <br> <br>.frm file stores table definitions. <br> <br>Data file extension is .MYD (MYData). <br> <br>Index file extension is .MYI (MYIndex). <br> <br> | Disk-based resources are InnoDB tablespace data files and its log files. The size of an InnoDB table is limited only by the size of the operating system file, typically 2GB. <br> | | Transaction Processing: <br> <br> | MyISAM tables prioritize performance, executing faster than InnoDB, but do not support transactions. <br> <br> | InnoDB supports transactions, foreign keys, and other advanced database features. <br> <br> | | SELECT, UPDATE, INSERT, DELETE Operations <br> | If you perform a large number of SELECTs, MyISAM is a better choice. <br> <br> | 1. If your data involves a large number of INSERTs or UPDATEs, InnoDB should be used for performance reasons. <br> <br> 2. DELETE FROM table, InnoDB deletes rows one by one. <br> <br> 3. LOAD TABLE FROM MASTER operations do not work with InnoDB. The workaround is to first convert the InnoDB table to MyISAM, import the data, and then convert back to InnoDB, but this does not apply to tables using additional InnoDB features like foreign keys. <br> <br> | | AUTO_INCREMENT Handling <br> <br> <br> | Each table has an internal handling of the AUTO_INCREMENT column. <br> <br> MyISAM automatically updates this column for INSERT and UPDATE operations, making it faster (at least 10%). Once the top value in the sequence is deleted, it cannot be reused. <br> <br>AUTO_INCREMENT values can be reset with ALTER TABLE or myisamch. <br> <br>For AUTO_INCREMENT columns in InnoDB, an index must include only that column, but in MyISAM tables, it can be part of a combined index with other columns. <br> <br>Better and faster auto_increment handling. <br> <br> | When you specify an AUTO_INCREMENT column for a table, InnoDB's table handle in the data dictionary includes a counter called the auto-increment counter, used to assign new values to the column. <br> <br>The auto-increment counter is stored only in main memory, not on disk. <br> <br>For the algorithm implementation of this counter, refer to <br> <br> How AUTO_INCREMENT Columns Work in InnoDB <br> <br> | | Table Row Count <br> | select count() from table, MyISAM simply reads the stored row count, but both tables operate the same when the count() statement includes a where condition. <br> <br> | InnoDB does not store the exact row count for a table, so executing select count(*) from table requires InnoDB to scan the entire table to calculate the number of rows. <br> <br> | | Locking <br> | Table-level locking <br> <br> | Row-level locking, providing non-locking reads in SELECTs similar to Oracle, and InnoDB tables may also lock the entire table if MySQL cannot determine the scan range, such as with update table set num=1 where name like "%aaa%" |

How to Choose Between MySQL Storage Engines MyISAM and InnoDB?

Although MySQL offers more storage engines than just MyISAM and InnoDB, these two are the most commonly used. Some site owners may not pay attention to the storage engine used by MySQL, but it is a significant aspect of database design. So, which storage engine should be used for a blog system?

Below, we compare the two storage engines.

Based on these nine differences and the characteristics of personal blogs, it is recommended to use MyISAM for personal blog systems, as the primary operations are reading and writing, with few chain operations. Therefore, choosing MyISAM can make your blog pages load faster than those using InnoDB, although this is just a suggestion. Most blogs should carefully choose based on actual circumstances.

Some considerations for choosing between MyISAM and InnoDB:

MYISAM and INNODB are two storage engines provided by MySQL. Each has its strengths and weaknesses. INNODB supports advanced relational database features like transactions and row-level locking, which MYISAM does not. MYISAM offers better performance and smaller storage space. Therefore, the choice of storage engine depends on specific application needs.

If your application requires transactions, you should undoubtedly choose INNODB. However, note that INNODB's row-level locking is conditional. Without a primary key in the where condition, it can still lock the entire table, such as with DELETE FROM mytable.

If your application prioritizes query performance, choose MYISAM. Its indexes and data are separate, and its indexes are compressed, making it more memory-efficient and faster for queries than INNODB. Compressed indexes also save disk space. MYISAM supports full-text indexing, which optimizes LIKE query efficiency.

Some argue that MYISAM is only suitable for small applications, but this is a misconception. If data volume is large, scaling the architecture, such as sharding and partitioning, rather than relying solely on the storage engine, is necessary.

Other perspectives:

INNODB is generally preferred now, mainly due to MYISAM's table-level locking, serial read-write issues, and lower concurrency due to table locking. MYISAM is typically not chosen for read-write intensive applications.

Regarding the default storage engine for MySQL:

MyISAM and InnoDB are two storage engines for MySQL. If installed by default, it should be InnoDB, as you can find default-storage-engine=INNODB in the my.ini file. You can also specify the storage engine when creating a table. Use show create table xx to see the relevant information.

Comparison Between InnoDB and MyISAM in MySQL

MyISAM:

Each MyISAM is stored on disk as three files. The first file name begins with the table name, and the extension indicates the file type. The .frm file stores table definitions. The data file extension is .MYD (MYData).

MyISAM tables can be compressed and support full-text searches. They do not support transactions or foreign keys. Transaction rollbacks are incomplete and lack atomicity. During updates, table locks are applied, reducing concurrency. If you perform a large number of SELECTs, MyISAM is a better choice.

MyISAM's indexes and data are separate, and its indexes are compressed, making memory usage more efficient. This allows loading more indexes, while InnoDB's indexes and data are tightly bundled, without compression, making InnoDB larger than MyISAM.

MyISAM caches indexes in memory, not data. InnoDB caches data in memory, which means the larger the server memory, the greater the advantage for InnoDB.

Advantages: Faster data queries, suitable for large amounts of select operations, and supports full-text indexing. Disadvantages: Does not support transactions, does not support foreign keys, low concurrency, not suitable for a large number of updates.

InnoDB:

This type is transaction-safe. It has the same features as BDB type and also supports foreign keys. InnoDB tables are fast. It has richer features than BDB, so if you need a transaction-safe storage engine, it is recommended to use it. It locks rows during updates, offering relatively high concurrency. If your data involves a large number of INSERTs or UPDATEs, for performance considerations, you should use InnoDB tables.

Advantages: Supports transactions, supports foreign keys, high concurrency, suitable for a large number of updates.

Disadvantages: Faster data retrieval, not suitable for a large number of selects.

For InnoDB tables that support transactions, the main factor affecting speed is that the default setting for AUTOCOMMIT is on, and the program does not explicitly call BEGIN to start a transaction, resulting in each insertion being automatically committed, which seriously affects speed. You can call begin before executing the SQL, grouping multiple SQL statements into a transaction (even if AUTOCOMMIT is on), which will greatly improve performance.

Basic differences: MyISAM type does not support advanced transaction processing, while InnoDB type does.

MyISAM tables emphasize performance, executing faster than InnoDB, but do not provide transaction support, whereas InnoDB provides transaction support and advanced database features like foreign keys.

Other comparisons:

MyISAM is a newer version of the IASM table with the following extensions:

Here are some detailed and specific differences:

Additionally, InnoDB's row locking is not absolute. If MySQL cannot determine the scan range while executing a SQL statement, InnoDB tables will also lock the entire table, for example, UPDATE table SET num=1 WHERE name LIKE "%aaa%".

No table type is universally perfect; choosing the appropriate type based on the business needs is key to maximizing MySQL's performance advantages.

Comparison of updates between InnoDB and MyISAM:

InnoDB's data organization is based on the primary key as a B+ tree. If no primary key is explicitly defined, InnoDB will select a NOT NULL UNIQUE key as the primary key. If none exists, InnoDB will create a 6-byte primary key. The primary key index points to the page, not the specific row location.

Non-incremental primary keys can slow down insertion, such as using a phone number or ID card number as the primary key, so using AUTO_INCREMENT wisely is recommended.

Large tables are not a problem, but operations like COUNT or high offset LIMITs are. You can replace a large LIMIT with a series of smaller ones:

LIMIT 0 1000 | LIMIT 1001 1000 | LIMIT 2001 1000

LIMIT 0 1000 | WHERE id>max_id1 LIMIT 1000 | WHERE id>max_id2 LIMIT 1000

For InnoDB, partitioning a table by a column to improve performance on a single server is not meaningful.

Insertion speed and query speed can sometimes be incompatible.

Claiming that InnoDB is unsuitable for COUNT operations is incorrect; MyISAM is equally slow, except that MyISAM caches the row count of the entire table, making COUNT operations on the entire table faster. If there is a query condition and it is not a primary key query, there is no difference. The reason primary key COUNT operations are slow is that InnoDB organizes data by the primary key and loads data during such operations.

InnoDB's page-based storage makes it easier for InnoDB to perform whole table caching and hot backups.

If a table has many indexes, InnoDB's update speed is greater than MyISAM's because InnoDB's secondary indexes link to the table's primary key, a logical value, whereas all MyISAM indexes link to the physical location of the data. If the physical location changes upon update, all indexes must be updated.

InnoDB does not store the specific row count of the table, meaning that when executing SELECT COUNT(*) FROM table, InnoDB has to scan the entire table to calculate the number of rows, whereas MyISAM can simply read the stored row count. Note that when the COUNT(*) statement includes a WHERE condition, both tables operate the same way.

Original Source: https://my.oschina.net/junn/blog/183341

❮ Cpp Keyword Intro Java Varargs Parameter ❯