Tagged articles
MyISAM
60 articles · Page 1 of 1
This article provides a comprehensive summary of MySQL indexing strategies including B+Tree and Hash structures, composite index leftmost prefix principles, and locking mechanisms covering transaction isolation levels, row/table locks, pessimistic/optimistic locking with practical SQL examples for InnoDB and MyISAM engines.
B+TreeIndexInnoDB
0 likes · 24 min read

This article walks through every stage a MySQL statement undergoes—from client connection, authentication, and (now‑removed) query cache, through parsing, optimization, execution, and storage‑engine access—highlighting the roles of InnoDB vs MyISAM, logging mechanisms, and best‑practice table design tips.
InnoDBMyISAMMySQL
0 likes · 14 min read

When interviewers ask about MySQL InnoDB vs MyISAM, go beyond transaction support and discuss six deeper differences—including count(*) performance, MVCC, storage layout, caching, crash recovery, and ideal use cases—to showcase comprehensive technical knowledge.
Database EnginesInnoDBInterview Tips
0 likes · 4 min read

This article compares InnoDB and MyISAM across transaction support, MVCC, storage structures, indexing, caching, and typical use cases, explains why InnoDB became the default engine in MySQL 5.5+, and provides concrete migration steps and performance‑tuning guidance.
InnoDBMyISAMMySQL
0 likes · 9 min read

The article explains that although kill ‑9 reliably kills a Linux process, using it in production can cause severe data corruption—especially with non‑transactional storage engines—making it unsuitable for safe program termination.
LinuxMyISAMkill
0 likes · 3 min read

The article explains common pitfalls that cause Spring @Transactional to fail or not roll back, such as incorrect method visibility, final or static modifiers, internal method calls, beans not managed by Spring, multithreading, unsupported database engines, misconfigured propagation, swallowed exceptions, and improper rollback settings, and offers practical solutions for each case.
AOPBackendJava
0 likes · 18 min read

This article explains the structural differences between MyISAM and InnoDB storage engines in MySQL, focusing on how each implements primary and secondary indexes using B+ trees, with code examples and diagrams to illustrate clustering versus non‑clustering index designs.
B+TreeIndexInnoDB
0 likes · 4 min read

The article shares a seasoned DBA’s firsthand comparison of MySQL and PostgreSQL, covering their histories, performance quirks, real‑world case studies, lock mechanisms, backup strategies, and practical selection guidelines to help developers decide which database best fits their workload.
InnoDBJSONBMyISAM
0 likes · 9 min read

This article compares MySQL's InnoDB and MyISAM storage engines across dimensions such as transaction support, locking, file structure, indexing, full‑text search, and COUNT(*) performance, helping developers choose the appropriate engine based on workload and consistency requirements.
InnoDBMyISAMMySQL
0 likes · 13 min read

This article explains the main directory layout of MySQL 8, the locations of data files, configuration files, system databases, and how InnoDB and MyISAM storage engines represent tables and indexes on the file system, including the use of system and file‑per‑table tablespaces.
ConfigurationDatabase DirectoryInnoDB
0 likes · 13 min read

This article compares MySQL's three common storage engines—InnoDB, MyISAM, and Memory—by examining their core features, locking mechanisms, transaction support, durability, foreign‑key capabilities, typical use cases, and provides concrete CREATE TABLE examples and a side‑by‑side feature matrix to help developers choose the right engine.
Database PerformanceInnoDBMemory
0 likes · 7 min read

This article explains MySQL's index limits for InnoDB and MyISAM engines, detailing the maximum number of indexes per table, the maximum columns per index, and provides practical recommendations on how many indexes a table should have to balance performance and maintenance.
InnoDBMyISAMMySQL
0 likes · 6 min read

This article explains how MySQL's count() works across storage engines, why InnoDB scans rows and can time out on massive tables, and presents practical alternatives such as using EXPLAIN rows, a dedicated count table, batch processing, or binlog‑to‑Hive for efficient row‑count estimation.
COUNTInnoDBMyISAM
0 likes · 14 min read

This guide explains MySQL 8's main directory layout, the locations of command binaries and configuration files, how databases and system schemas are represented on the file system, and the differences between InnoDB and MyISAM storage engines, including tablespace and file naming conventions.
Database FilesInnoDBMyISAM
0 likes · 13 min read

This article explains MySQL storage engine concepts, compares InnoDB, MyISAM, and MEMORY, describes how data is stored in shared tablespace versus file‑per‑table modes, and provides configuration commands for switching modes, changing default engines, and inspecting or altering a table’s engine.
InnoDBMyISAMMySQL
0 likes · 6 min read

MySQL’s pluggable storage‑engine architecture lets each table use a specific engine, with InnoDB (the default in MySQL 8.x) offering row‑level locking, ACID transactions, foreign‑key support, crash recovery, MVCC and a buffer pool, while MyISAM provides only table‑level locking, no transactions, and is suited mainly for read‑intensive workloads lacking recovery requirements.
InnoDBInterviewMyISAM
0 likes · 9 min read

This article explains the purpose, recommended settings, and tuning tips for MyISAM‑related MySQL variables such as key_buffer_size, read_buffer_size, read_rnd_buffer_size, bulk_insert_buffer_size, myisam_sort_buffer_size, myisam_max_sort_file_size, myisam_repair_threads, and myisam_recover, helping DBAs optimize indexing and query performance.
BuffersConfigurationMyISAM
0 likes · 4 min read

The article analyzes a replication failure where mysqldump creates duplicate primary‑key errors on a MyISAM table, explains why the --single-transaction option only guarantees consistency for InnoDB, and provides solutions such as converting tables to InnoDB or using Xtrabackup.
InnoDBMyISAMMySQL
0 likes · 15 min read

This article provides a systematic deep‑dive into MySQL table‑lock mechanics, comparing storage engines, explaining the advantages of table locks, detailing lock acquisition and release rules, showing how to monitor lock statistics, and revealing why MyISAM can deliver superior performance for heavy concurrent insert and select workloads despite using only table‑level locking.
Database PerformanceLock MechanismMyISAM
0 likes · 7 min read

This article explains how MySQL's count() works across different storage engines, compares the performance of various count() forms, and offers practical strategies—such as using EXPLAIN rows, auxiliary count tables, and batch processing—to obtain accurate or approximate row counts efficiently even on massive tables.
COUNTInnoDBMyISAM
0 likes · 13 min read

This article explains MySQL locking mechanisms—including table‑level, row‑level, shared and exclusive locks, as well as gap locks—covers the differences between MyISAM and InnoDB engines, shows how to monitor lock statistics, and provides practical optimization tips for reducing lock contention.
InnoDBLocksMyISAM
0 likes · 9 min read

This article examines MySQL's ON DUPLICATE KEY UPDATE clause, explains its official behavior, runs experiments with primary and multiple unique indexes, compares InnoDB row‑level locking to MyISAM table‑level locking, and highlights performance and deadlock risks that developers should avoid.
InnoDBMyISAMMySQL
0 likes · 8 min read


ITPUBJan 28, 2022 · Databases
This article explains MySQL's index storage structures, the difference between clustered and secondary indexes, and demonstrates six typical scenarios—wildcard LIKE, functions on indexed columns, expression calculations, implicit type conversion, left‑most rule violations in composite indexes, and OR conditions—that cause indexes to become ineffective and lead to full‑table scans.
IndexInnoDBMyISAM
0 likes · 16 min read

The article explains MySQL 5.7's engine‑dependent prefix index length limits, demonstrates how utf8mb4 reduces the maximum indexable column size for MyISAM tables, provides test cases with SQL code, and offers practical solutions such as prefix indexes, composite indexes, and CTAS for large datasets.
MyISAMMySQLPrefix Index
0 likes · 6 min read

This article explains the fundamentals and structures of database indexes—including binary trees, red‑black trees, B‑Tree, B+Tree, hash indexes—and details how MySQL’s InnoDB and MyISAM engines implement clustered and non‑clustered indexes, covering their characteristics, storage files, and query behavior.
B+TreeDatabase IndexesInnoDB
0 likes · 9 min read

This article documents a real‑world incident where a MyISAM table in a Percona XtraDB Cluster caused data inconsistency and node self‑shutdown, analyzes the root cause, and describes how using pt-online-schema-change together with proper engine conversion restored consistency across the cluster.
InnoDBMyISAMMySQL
0 likes · 14 min read

This article compares MySQL's COUNT(*), COUNT(1) and COUNT(column) functions, explains how each handles NULL values, analyzes performance differences on MyISAM and InnoDB engines, and provides practical recommendations for writing efficient count queries and existence checks.
COUNTCOUNT(1)COUNT(column)
0 likes · 9 min read

This article explains why MySQL's auto‑increment counter can be lost after a server restart for InnoDB tables, compares the behavior with MyISAM, demonstrates the problem with practical SQL examples, and shows how MySQL 8.0 fixes the counter logic.
InnoDBMyISAMMySQL
0 likes · 7 min read

This article explains MySQL index fundamentals, the B+Tree structure, differences between MyISAM and InnoDB implementations, practical indexing strategies, and performance‑tuning techniques such as configuration parameters, EXPLAIN analysis, and optimal index design for real‑world queries.
B+TreeIndexInnoDB
0 likes · 31 min read

This article explains MySQL's B‑Tree index fundamentals, compares MyISAM and InnoDB implementations, outlines how B+Tree structures work, and provides practical guidelines for designing effective indexes and tuning MySQL configuration to boost query performance.
B+TreeInnoDBMyISAM
0 likes · 27 min read

The article explains why MySQL raises a GTID consistency error when a single transaction updates both InnoDB and MyISAM tables, demonstrates how to reproduce the issue, and evaluates three mitigation strategies: separating the updates, converting MyISAM to InnoDB, or disabling ENFORCE_GTID_CONSISTENCY.
GTIDInnoDBMyISAM
0 likes · 6 min read

This article explains the fundamentals of MySQL indexes, the B+Tree data structure, differences between MyISAM and InnoDB implementations, practical tips for creating effective composite indexes, and essential server configuration and SQL tuning techniques to dramatically improve query performance.
B+TreeDatabaseOptimizationIndexDesign
0 likes · 29 min read

This article explains the complete MySQL architecture, covering its connection, service, storage‑engine, and system‑file layers, the role of each component such as connection pools, query cache, parser, optimizer, executor, and the differences between InnoDB and MyISAM, providing practical insights for developers and interview preparation.
Database ArchitectureInnoDBMyISAM
0 likes · 13 min read

This article explains why MySQL uses B+‑tree indexes, describes the principles of B‑tree and B+‑tree structures, compares MyISAM and InnoDB index implementations, and provides practical optimization tips such as using auto‑increment primary keys, the left‑most prefix rule, and proper configuration settings.
B+TreeIndexInnoDB
0 likes · 22 min read

This article explains why MySQL uses B+ trees for indexing, describes the underlying principles of various index types, compares MyISAM and InnoDB implementations, and provides practical optimization guidelines such as using auto‑increment primary keys, left‑most prefix rules, and configuration tuning.
B+TreeIndexInnoDB
0 likes · 22 min read

This article explains how MySQL stores indexes for the MyISAM and InnoDB engines, comparing clustered (primary) indexes and secondary indexes, illustrating their B+‑tree structures, showing SQL examples, and discussing the advantages and disadvantages of each storage method for database optimization.
B+TreeInnoDBMyISAM
0 likes · 10 min read

This article explains the concept of MySQL compressed tables, highlights strong compression products, discusses why and when to use them, outlines their advantages and drawbacks, details supported compression algorithms, and provides step‑by‑step examples for both MyISAM and InnoDB engines along with their impact on B‑tree pages and the InnoDB buffer pool.
Compressed TablesInnoDBMyISAM
0 likes · 13 min read

This article explains the different lock mechanisms used by MySQL storage engines—including MyISAM table‑level locks and InnoDB row‑level locks—covers lock compatibility, scheduling, transaction properties, optimistic locking, and practical strategies to avoid lock conflicts and deadlocks.
InnoDBLocksMyISAM
0 likes · 14 min read

This article walks through the evolution of MySQL index implementations—from hash tables and binary search trees to AVL, red‑black, B‑trees and finally B+ trees—explaining their performance trade‑offs, disk‑I/O considerations, and how InnoDB and MyISAM store data and indexes differently.
B+TreeDataStructureIndex
0 likes · 21 min read

This article provides a comprehensive, step‑by‑step guide to MySQL's locking mechanisms—including table, row, gap, and deadlock handling—covering both MyISAM and InnoDB engines, complete with SQL examples, visual demonstrations, and practical tips for high‑concurrency scenarios.
GapLockInnoDBMyISAM
0 likes · 21 min read

This article provides a detailed overview of MySQL's locking mechanisms—including table, row, gap, and deadlock handling—across MyISAM and InnoDB storage engines, with practical code examples, performance considerations, and solutions for high‑concurrency scenarios.
InnoDBLock MechanismMyISAM
0 likes · 19 min read

This article explains why MySQL requires locking, compares table and row lock types, details MyISAM and InnoDB lock mechanisms—including optimistic vs. pessimistic locks, lock algorithms, and deadlock handling—while providing practical SQL examples and prevention strategies.
Database LocksInnoDBMyISAM
0 likes · 16 min read

This article explains the MySQL COUNT function, compares COUNT(*), COUNT(1) and COUNT(column) usages, discusses common interview questions, and details the specific optimizations MyISAM and InnoDB apply to COUNT(*) queries, concluding with best‑practice recommendations.
COUNTInnoDBMyISAM
0 likes · 10 min read

This article explains the different forms of MySQL's COUNT function, compares COUNT(*), COUNT(1) and COUNT(column), reveals engine‑specific optimizations for MyISAM and InnoDB, and provides guidance on which form to use in practice.
COUNT functionInnoDBMyISAM
0 likes · 10 min read

This article explains why MySQL can exhaust disk space when implicit temporary tables grow too large, describes how memory limits and storage‑engine choices affect their creation, and provides practical configuration and query‑optimization techniques to avoid service interruptions.
InnoDBMyISAMMySQL
0 likes · 8 min read

This article explains how library indexing inspires database indexing, introduces binary search trees, AVL trees, B‑Tree and B+Tree structures, and details InnoDB and MyISAM storage mechanisms, page organization, clustered versus non‑clustered indexes, and practical index‑optimization advice.
B+TreeB-TreeInnoDB
0 likes · 19 min read

This article explains MySQL's lock mechanisms, comparing MyISAM's table‑level locks with InnoDB's row‑level locks, covering lock types, compatibility, transaction isolation levels, deadlock scenarios, and practical tips to avoid lock contention and improve database concurrency.
Database ConcurrencyInnoDBLocks
0 likes · 22 min read

This article explains the mechanisms behind MySQL's SELECT COUNT(*) operation, comparing InnoDB's row‑by‑row scan with MyISAM's constant‑time meta count, and explores execution steps, visibility rules, data structures, and performance implications.
COUNTInnoDBMyISAM
0 likes · 16 min read


ITPUBApr 23, 2019 · Databases
This article explains MySQL's three lock types, the storage engines that use them, detailed MyISAM and InnoDB lock behaviors, gap‑lock pitfalls, optimization tips, and how to monitor lock contention with built‑in status variables.
Database PerformanceInnoDBMyISAM
0 likes · 10 min read


ITPUBFeb 8, 2018 · Databases
This article explains MySQL's row‑level, table‑level, and page‑level locks, maps them to storage engines, details MyISAM and InnoDB lock types, describes the four internal lock queues, and provides practical optimization and monitoring techniques to improve concurrency and performance.
InnoDBMyISAMMySQL
0 likes · 10 min read

This guide explains MySQL's configuration fundamentals, how MySQL reads options, variable scopes, dynamic settings, common pitfalls, recommended habits, and provides a minimal yet comprehensive example my.cnf with detailed explanations for each section.
ConfigurationInnoDBMyISAM
0 likes · 37 min read


ITPUBOct 17, 2017 · Databases
This article explains MySQL's lock types across storage engines, details MyISAM and InnoDB locking behaviors, describes the four lock queues, outlines gap‑lock characteristics, and provides practical optimization and monitoring techniques to reduce lock contention.
Database PerformanceInnoDBMyISAM
0 likes · 10 min read

An in‑depth comparison of MySQL’s default storage engines—MyISAM and InnoDB—covers their evolution, core features, performance benchmarks, locking mechanisms, transaction support, file structures, and practical guidance on choosing the right engine for different workloads.
Database PerformanceInnoDBMyISAM
0 likes · 8 min read


ITPUBNov 15, 2016 · Databases
This guide details the most important MySQL server configuration settings—including connection limits, file handling, cache sizes, MyISAM and InnoDB options—explaining their purpose, recommended values, and how they impact performance and stability.
ConfigurationInnoDBMyISAM
0 likes · 22 min read

Learn why MySQL tables develop fragmentation, how to measure it using Data_free in INFORMATION_SCHEMA, and the appropriate commands—OPTIMIZE for MyISAM and ALTER ENGINE for InnoDB—to safely reclaim space, with practical scheduling tips to minimize impact on production workloads.
InnoDBMyISAMMySQL
0 likes · 4 min read

MyISAM, the original MySQL storage engine used for over two decades, is now limited and discouraged in MySQL 8.0 due to its lack of transactions, table‑level locking, and crash recovery, with InnoDB taking over its former advantages.
InnoDBMyISAMMySQL
0 likes · 2 min read


ITPUBOct 13, 2016 · Databases
The article clarifies that nontransactional statements are SQL commands operating on non‑transactional tables such as MyISAM, explains how MySQL parameters max_binlog_stmt_cache_size and max_binlog_cache_size control memory for these statements, and demonstrates the difference with transactional InnoDB tables through concrete command‑line examples.
InnoDBMyISAMMySQL
0 likes · 4 min read


ITPUBApr 29, 2016 · Databases
This guide explains why MySQL tables become fragmented, how to detect fragmentation using information_schema queries, and how to defragment tables safely with OPTIMIZE, ALTER TABLE, or MySQL startup options while noting engine‑specific behavior and best‑practice cautions.
ALTER TABLEInnoDBMyISAM
0 likes · 5 min read


ITPUBDec 11, 2015 · Databases
The article examines two common MySQL master‑slave delay scenarios—slow ALTER operations and MyISAM table‑level locks—explains why they cause replication lag, and offers practical mitigation steps such as scheduling problematic SQL, isolating lagging slaves, and tuning binlog write settings.
ALTERMaster‑SlaveMyISAM
0 likes · 3 min read

Learn how to fine‑tune MySQL by adjusting key general, MyISAM, and InnoDB parameters—such as max connections, buffer sizes, query cache, and log settings—to improve performance, stability, and resource utilization based on recommended percentages of system memory.
ConfigurationInnoDBMyISAM
0 likes · 3 min read
