MySQL Tips, Snippets & Articles

Best practices	Deadlocks	Disk I/O measurement
Database design	Deadloacks and retries	InnoDB I/O Capacity
Database basics	Identify deadlocks	Understanding I/O capacity
What is a database?	Insert deadlocks and Serializable	InnoDB
Basics of designing a database	Prevent, handle deadlocks	InnoDB query optimisation basics
Build a database from use case analysis	Installation & setup	InnoDB_buffer_pool_size
Basics	Access MySQL instance on VM	Optimise InnoDB table counts
Design for query speed	Android web server with MySQL	InnoDB vs MyISAM
Many:many basics	Build MySQL in Windows	Benchmarks
Model repository	Change MySQL data directory in *Nix	InnoDB vs MySAM query performance
MyISAM concurrency	Link MySQL, SQL Server	Locks & waits
MySql is a better NoSql	MySQL to MariaDB	Find locks & waits
When and how to project child rows	Reinstall on Windows	Hot rows, Skip_Locked, Nowait
Foreign Keys	Ubuntu/Debian repo	Wait events per thread
Basics	with Magento	Performance_schema
Cascade	Access & connection issues	Mine performance_schema for transactions
On Delete Set Null	Access denied errors	Performance analysis with perf
Alter engine in one or all tables	Access MySQL on another workstation	Performance_schema overview
Compression	Connect to MySQL from Base	Report transactions
Database and table size limits	Connect to MySQL from Excel	Query optimisation
Database templates with MySQL	Connect to MySQL server on a network	How MySQL chooses between indexes
Database tutorials on the web	Connectable IP addresses	Indexing basics and queries
Managing permissions	Connection fails unless WorkBench connected	Investigate slow queries
MySQL limits	Connections and states	Multi-range read optimisation explained
Normalisation: fine points	GUI MySQL connection through a firewall	Nested loop join
Round-robin database	Lost MySQL password in Windows	Optimise BETWEEN queries
Switch MyISAM tables to InnoDB	Network connection issues with Windows	Optimise geospatial queries
Updatable view as constraint	Connect via SSH, SSL	Optimise Group By
Why not use MySQL bit columns?	Connect to MySQL via SSH	Optimise MyISAM queries
Workarounds for no Check Constraint	Connect to MySQL with SSL	Optimise Order By with Limit
History, audit, point-in-time design	SSH basics	Optimise query cache efficiency
Audit DDL	SSH connection to MySQL from Linux	Optimise range condition with Order By
Audit plugins in 8.0	SSH tunnel to MySQL Server	Optimiser cost model
Consolidated audit log	WorkBench	Percona query analyser
Data-driven audits	Connect WB to remote MySQL server	Simplify query to speed it up
McAfee audit plugin	Remotely administer MySQL on W2K	Sluggish query? Look at the keys
Point-in-time architecture, audit trails	WB alternatives	Optimise subqueries
SCD (slowly changing dimensions)	Windows connection to remote MySQL server	Derived table instead of a subquery
Timed versioning in MariaDB	Charset issues	In() vs Exists() before 5.6 and since
Managing duplicates	Charset/collation basics	Materialise the subquery: months to secs
Consolidate duplicates	ANSI character set	Subquery optimisation basics
Delete rows with dupe IDs for same info	Change column charset	Ranking
Models & modelling	Character set and collation basics	Optimise rank data
Data modelling 101	Charsets, performance, versions	Ranking in 8.0
Model repository	Comparing ints with chars	Queries
Modelling tools	Delete special characters from text	Common MySQL queries
Partitioning tables	Euro symbol	Basics of PREPARE
Partition with events and sprocs	Get out of MySQL charset hell	Divide and conquer
Partitioning tips	MySQL client program and charsets	Emulate Common Table Expressions
When to partition	UCA-compliant collations	Emulate materialised views
Sample databases	Why and how to migrate to utf8mb4	FlexViews
Northwind DB for InnoDB	Windows and character sets	Prepare a list of values
Sakila DB	Utf-8	Query attributes
World DB	Migrate to utf8mb4	Return query result as XML
Generate random data	Utf-8 collations	Replication
Generate rows of random data	Utf-8 everywhere	Basic replication 8-step
Online random data	Utf8 to utf8mb4	Setup: 4 methods
Populate table with random data	Utf8 with MySQL & PHP	and Load Data Infile
Special designs	In the cloud	and upgrades
Design for latitude/longitude search	MySQL setup in the cloud	Async and group replication
FIFO queue	Oracle Heatwave	Booking.com: automation disaster
Job queue	Oracle Heatwave & MySQL	Encrypted replication
Multi-terabyte databases	Oracle Heatwave MySQL performance	InnoDB Replica Set
Entity-attribute-value (EAV) model	Installation issues	Master with replay slave
EAV designs and queries	Sys config error in 8.0	Multi-master replication
Advanced	Windows Installation issues	Multi-master with N read slaves
Compression with mysqlbinlog	Applying Configuration hangs	MySQL shell plugins
Connect to MySQL from Oracle	Cannot connect to any specified host	Reset replica/slave 5-step
Connect to MySQL from SQL Server	ExecSecureObjects fails	Ring replication-why not
Matrix math in SQL	Failure on VC library issue	Schema changes
MySQL Fabric	MySQL fails to start	Slave out of sync
MySQL internals	On Mac OS	Swap source & replica
Q&A; for DBAs	Find config files	Synchronise with rsync
Requests for MySQL 5.8	MySQL client program on Mac	Troubleshoot replication
Storage	Start MySQL server manually	Useful articles
Storage: the near future	WAMP	Group replication
Workload analytics	Add Perl to WAMP	Group replication
Bugs, workarounds	WAMP download & installation	Who is primary master?
Tmp table is full	WampServer updates	High Availability
Building MySQL	Java	Automatic failover since 8.0.22
Small footprint	Intro tutorial	Automatic source list
C, C#, C++	Set up Java for MySQL	Galera
How to build MySQL 8.0 C API	Background execution	Switch master on failover
C, C++, dates, datetimes	Multiple queries in stored procedure	InnoDB Cluster
Data warehouses	Manipulating resultsets	Set up InnoDB Cluster
Data mart or data warehouse?	Alternative to Order By Rand()	and group replication
Dimensional modelling	Do it in mysql or an API language?	in IBM cloud
Improve MySQL data warehouse performance	Export a query result to a file	InnoDB Cluster vs NDB Cluster
MySQL data warehouse	Order By RAND()	Migrate from Galera
MySQL data warehouse slideshow	MariaDB	MySQL Router Restful API
MySQL data warehouse--essays	Compare MariaDB, MySQL	Quick Setup
Summary tables	ISPs that support MariaDB	Single- vs multi-primary
Database administration	MariaDB market share	Tutorial slideshow
MySQL Glossary	MariaDB-MySQL compatibility	MySQL Operator
MySQL config settings	Migrate to MySQL	MySQL Operator for Kubernetes
Add auto_increment PK to table	Features	Migrate MySQL to Kubernetes
Add column to a table	Changes in 10.0	Metrics
Architectures	Changes in 10.1	Estimate max_connections for slave
Automate database copy	Changes in 10.2	Estimate replication capacity
Automatically kill runaway queries	Changes in 10.3	Slave lag
Basic database synchronisation	Changes in 10.4	Security
Big deletes	Changes in 10.5	Best practices
Biggest tables	Changes in 10.6	Caching_sha2_password
Compare MySQL configurations	Multi-source replication	Dual passwords
DNS aliases	PCRE regular expressions	Encryption at rest
Find database objects by name	Roles	ldap auth group against ds-389
Is the MySQL server running?	Show Alter Table, Load Data progress	Load Data Local security
Log DDL changes	Virtual columns	MySQL & OpenSSL
Mass deletions	MariaDB storage engines	MySQL Shell & password store
Move MySQL data	Cassandra engine	Password validation component
Performance_schema	Connect engine	Prevent SQL injection attacks
Perl script to compare database structures	FederatedX engine	RBAC kernel
Recover database from hard drive	Open Query Graph engine	Security in the real world
Rename Table, user privileges	Sequence engine	Audits
Show Processlist	Sphinx engine	Audit sensitive data
Sys schema	Spider engine	Poor man's audit
Tmp table is full	TokuDB engine	Stored routines
Where is my datadir?	XtraDB engine (InnoDB replacement)	Build IN() list for a stored procedure
Admin Basics	Migration	Call stored procedure with OUT parameter
Find the pid file	Basic database export script	Error handling docs
Licencing	Export table to CSV	Excellent tutorial on stored routines
Move MySQL to another *Nix disk	Import multiple csv files	How to call a stored procedure from PHP
Online MySQL config wizard	Migrate users	How to debug MySQL stored routines
Password() function	Migration: utf8 to utf8mb4	Look up routine's params
Recreate root user account	Open source migration tools	Nest cursors
Reset root password	Oracle to MySQL	Raise custom error
SANs and MySQL	Recompute auto_increment values on data export	Slowness of stored procedures
Uninstall in Linux	SQL Anywhere to MySQL	Sprocs if necessary, not necessarily sprocs
Upgrading MySQL	From SQL Server	Where's that stored routine?
Versions vs Operating Systems	General help	Workaround for no dynamic cursors
What config file is MySQL using?	Port stored procedures	Debug
Sock files	SQL Server backup to MySQL	Debug in Visual Studio
Can't connect via socket	Miscellaneous	Debug methods
Sock file missing	Batch scripts	Strings
Windows	Coding style and standards	Fix charset-encoding mismatches
Fix for 5.6.x Windows installer bug	Deadlines	Parse a string into columns non-procedurally
Install in Windows	Displaying images in web pages	Replace first instance only of a substring
Install in Windows Vista	Format queries	Search and replace in strings
Installation of Windows service fails with Error 0	Import fixed-width data	Word count
Installer offers no new installations	Import large SQL script to remote DB	Triggers
Manual installation in Windows	Load_File() and AppArmor	Get next autoinc value in Before Trigger
MySQL won't start in Windows	MySQL books	Manage INSERT failures
MysqlInstanceConfig failure	MySQL downloads	Update table B on Insert into table A
Run mysql commandline client in Windows	Run external command	Use a Trigger to block an Insert
Uninstall issue with 8.0	SQL formatter	Versions
When the MySQL GUI installer fails	Version history graph	Client-server tool compatibilities
Backup & recovery	UUIDs	History
Basic backup strategy	UUID basics	Backward-incompatible changes 5 & up
Best backup practices	UUID perrformance	Brief version history
Fast InnoDB snapshots	MySQL and web apps	History
Incremental backup, recovery	So you want to write an app ...	MySQL 8.0
InnoDB backup methods	Comparison of HTML editors	8.0 Overview
InnoDB recovery	MySQL and Java	8.0 All new features
Point-in-time recovery	Connector/J and time zones	8.0 Windows Installer gotchas
Recover from a crash	MySQL with Eclipse	8.0 Release notes
Recover from dropping mysql db	NetBeans 6.5.1: not ready for prime time	8.0 and Perl
Recovery via mysqlbinlog	Oracle SQL Explorer	8.0 audit plugins
Restore databases via file copy	Basics	8.0 Auto_increment fixed
Undrop for InnoDB	Get started with Java	8.0 Check Constraint
Clone a MySQL instance	Java intro	8.0 Connector/NodeJs
Cloning plugin manual page	Tomcat, Java, MySQL	8.0 Data locking visibility
Cloning benchmark	MySQL and Node.js	8.0 Dynamic Queries from Sys Schema
Cloning plugin: intro	MySQL-Node.js basics	8.0 Embedded server retired
Data dump utilities	Why Node.js?	8.0 First impressions
Benchmarks	MySQL and PHP	8.0 information_schema
High performance parallel dump	Setup	8.0 Manual
Mysqldump and consistency	8.0 and auth plugins	8.0 Milestone release features
Mysqldump vs mysqlpump	Configure MySQL & PHP in Windows	8.0 New defaults
PHP dump script	Configure MySQL and PHP on the Mac	8.0 New error logging
Speed up dump imports	Install & configure Apache & PHP in Linux	8.0 Ranking
Suppress definer with dump	PDO vs. mysqli	8.0 Regular expressions
Windows backup dump script	PHP and MySQL in Windows	8.0 Temp tables, Blobs & JSON
Windows, Powershell & mysqldump	PHP include files & DB-enabled apps	8.0 Trailing spaces
Shell dump	Component architecture	8.0, JDBC, timezones
Copy database with shell dump	Add component	8.0.27 Faster CRC32-C computation
Dump & load in Shell	Basics	Installer: failed security check
Shell dump array args	Call a stored procedure from PHP	Table encryption
Shell dump with compression	Callback	Threadwise memory use
Xtrabackup	Error reporting	8.0 Aggregation
Download, docs &c;	Master-detail data	Histograms
In Windows	Multi-queries	Rollup improvements
Connections	MySQL-PHP basics from Dev Shed	8.0 and PHP
Connection to the server was lost	Pagination	8.0 PHP and Document Store
Log fills with sha256_pswd warnings	Populate a dropdown in PHP: example	Default authentication
Log MySQL logins	Populate one dropdown from another	mysql_xdevapi
Password checks	Query retrieval and display	8.0 Charsets & collations
Remote connection	Runtime data type discovery	8.0 Latin1, Utf8mb4, Scalability
SSL setup with Ubuntu	Session setup	8.0 New collations
Too many connections	MySQL, PHP character sets and collations	8.0 UTF8 Improvements
Data recovery	Escaping text	Accents and case
Recover InnoDB tables	PHP-MySQL charset basics	Charsets and UDFs
Database statistics	Bad Request Errors	Collation slideshow
Table statistics	Basic PDO example	How to choose a charset
Information_schema	Dynamic dropdowns with Ajax	Utf8 to utfmb4
IS in MySQL 5.6	Holiday dates	8.0 Database management
IS scaling in MySQL 8.0	Insert PHP array into MySQL table	8.0.14: Lateral derived tables
Table info	Jobs that run a long time	Clone across servers
Maintenance	List child tables	Information_schema
Estimate data sizes by engine	MySQL, PHP and SSL	Instant Alter table
Fix corrupted grants table	PEAR: usage notes	Millions of tables
High CPU usage	Prevent SQL injection attacks	Mysql.proc table is gone
Lock out non-root users temporarily	register_globals	Data Dictionary
Log message timezone	Show resultset as a bar graph	Hidden tables
Purge binary logs periodically	Transaction handler	Protected tables
Memory use	Validate email address input	Recover table DDL
and performance_schema	Charsets	SDI files
Estimate memory required	Charsets with MySQL & PHP	Transactional data dictionary
Memory stress, Linux and NUMA	MySQL, PHP and charsets	Unified data dictionary
Optimise MySQL memory usage	phpMyAdmin	Migration & upgrade
Overcommit and added memory	Create a stored procedure in phpMyAdmin	How to upgrade
Track memory use	Install	Migrate from older collations
Server crashes	Snippets	Upgrade from earlier version
Overall approach	Connect using object oriented syntax: examples	8.0 Upgrade Checker
Manage users	Dynamic variable names	8.0 Upgrade from 5.7
Document MySQL users	Generate query for names linked to attributes	8.0 Upgrade without breaking old app
Root access from everywhere?	Snippet plugin for MySQL Workbench	Tablespaces
Scaling	Utility	General Tablespaces: no FRM files
Best practices	Create Table script	Tablespace encryption
Big data volumes	MySQL and Python	8.0 GIS
Slideshow	CTEs with Python	GIS Overview
Upgrades	Debug MySQL in Python	GIS Basics
Prepare for upgrade	IN() and Python	GIS Geography
Shell upgrade checker	MySQL and Windows	GIS indexes in InnoDB
Upgrade 5.0->5.6	Connect to MySQL from SQL Server	GIS Spatial reference systems
Upgrade 5.6->5.7	Connect to MySQL from Excel	GIS Tutorial
Upgrade 5.7->8.0	Connect to MySQL from Visual FoxPro	8.0 InnoDB
Windows	Connector/ODBC and Utf8mb4	8.0 Contention-aware transactions
Windows cmd line and MySQL	Getting started with MySQL in Windows	8.0 GIS indexes
Windows uninstall failure	Load Data Infile in Windows	8.0 Huge page support
Date & Time	Map MySQL data types to .NET	8.0 InnoDB large object indexes
Data uniqueness in a period	MySQL and Microsoft Access	8.0 Instant Add Column for InnoDB
Epochs	ODBC and last_insert_id()	8.0 Locking read enhancements
Fiscal week of a date	The notorious Windows backslash	8.0 New lock-free scalable redo log
Fractional seconds	VB/ADO connection string	8.0 Tracking foreign keys
Milliseconds	Visual C++ redistributables	8.0 What's new with InnoDB
MySQL date/time inconsistencies	MySQL for Visual Studio	8.0 JSON
Resample into date-time bins	MySQL and Visual Studio 2019	Document store, date & time
Spring forward: the missing hour	MySQL and Visual Studio 2005	Indexing on JSON elements
The 2038 problem	Call a stored procedure: snippet	JSON Aggregation Functions
Time zone conversion	Dynamically bind MySqlData.dll: snippet	JSON audit logs
GUI Tools	Basic query methods	JSON Examples
OS-based MySQL GUIs	Retrieve a scalar result	JSON representation of row
HeidiSQL	Execute a non-SELECT command	SQL to JSON and back
Web-based MySQL GUIs	Prepared statements	Windowing with JSON functions
phpMyAdmin	Assemble query strings	8.0 Key and reserved words
TheUsual	Does the column value need quotes?	New reserved words
InnoDB	Execute a query, return a DataTable	Table of keywords
InnoDB architecture	DataGridView	8.0 New syntax
B+Tree index structures in InnoDB	Find the current row after a sort: snippet	Windowing functiions
Checkpoint Age	DataGridView: column search	Windowing instead of user vars
Internal ibdata structure	DgvRowFinder class	Common table expressions
InnoDB config variables	DgvRowFinder example caller	CTE documentaiton
Adaptive hash index	Simple Column Find dialog	CTE slideshow
Calculate InnoDB log file size	DataSets	CTEs 1. Introduction
Change buffer	Multi-key relations	CTEs 2. Series
Configuration settings	Just-in-time lookup grid	CTEs 3. Hierarchies
Large page support	DataRetriever class	Limit runaway CTEs
Track buffer pool use	Cache class	Sequences & ranges with CTEs
Tweak the InnoDB cache	Just-in-time DataGridView	8.0 Optimisation
Automate node provisioning	Example caller	Optimisation home paqe
Bulk Inserts & Load Data Infile	Overview	1M IO-bound QPS
Changing tables from MyISAM	Setup MySQL test app	AntiJoin
Datafile locations	Create a project	Hash Join
How to shrink or purge ibdata	Add a reference to MySQL	New optimisations
InnoDB and FullText	Set up the form	Optimisation improvements
InnoDB vs MyISAM	Write a MySQL connection class	Optimisation slideshow
InnoDB vs TokuDB benchmarks	TreeViews	Optimiser assesses data buffering
InnoDB: Caveat usor	Basic TreeView	Skip index dives with Force Index
Job progress info	Stored procedures and Connector.NET	8.0 Performance
Load Data and the Buffer Pool	Call stored procedure	3D XPoint vs SSD
Lower_case_table_names	OUT parameter test case	Configure for performance test
Parallel DDL operations	MySQL Comparisons	Connections & scalability
Separate table for long values?	MySQL vs Oracle, from Oracle	I/O bound benchmarks
Slideshow overview	MySQL vs PostgreSQL	Performance tuning
Table does not exist	MySQL errors	Scalability, performance
Table exists, can't be accessed	Error Handlers & Get Diagnostics	8.0 Replication
Tables with with no primary key	How to raise an error in MySQL	8.0.16 replication improvements
Tablespaces	MySQL client errors	HA improvements in 8.0.17
Updating stats	MySQL server errors	Monitor with performance_schema
UUIDs as primary keys?	Server will not start (Windows)	Replication improvements
Data recovery	Using SIGNAL	Replication, transaction metadata
Recover from InnoDB corruption	Validate data with Triggers and Signal	Group replication
Recover table structure	MySQL Shell	Synchronous or not?
Tables could not be fetched	Commit, rollback in MySQL Shell	8.0 Security
I/O configuration	Documentation	How to grant privileges
I/O tunng: o_direct or fsync?	Dump & Load	Lock account on failed logins
Linux and Doublewrite	MySQL Shell and arrays	SSL
Locking & contention	Shell reporting, monitoring	Tablespace encryption
Comprehensive guide to locking	Shell Setup & admin	Authentication
Data locks 1-Intro	OLAP,	8.0 Grant privileges tutorial
Data locks 2	Columnstore for MariaDB	Authenticate against Active Directory
Data locks 3: deadlocks	From ETL to ELT	Authentication plugins
Data locks 4: scheduling	OLAP with MySQL	Manage passwords
Data locks 5: concurrent queries	Query Analytics in 8.0	Multifactor authentication
Data locks examples	Reports	Password reuse policy
Deadlocks & concurrent inserts	Column header for a week	Roles
Find metadata locks & waits	Open source report writers: comparison	Basic syntax
Gap locks	Report generators	Intro by Giuseppe Maxia
Locking tutorial	Optimisation, performance	List roles and users
Locks with foreign keys	Primers	Listing roles
Row locks that block Inserts	Primer on index design for queries	Overview
RW locks & contention	17 config settings	White papers
Monitor	Analyse & tune MySQL	8.0 Shell
Innotop: Monitor InnoDB	Query optimisation slideshow	JSON imports
Monitoring MySQL	50 MySQL performance tips	Shell for Devops
Optimise	8.0 improvements over 5.7	What's new with shell in 8.0?
Dump & load buffer pool	8.0 slower than 5.7	8.0.13 changes
Optimise Inserts	Beware straight lines	Multiple bind addresses
Optimise MySQL & InnoDB performance	Buffer pool dump & load	Summary
Optimise table size	Engine Insert performance	8.0.14 changes
Optimise updates	Find slowest queries	Admin Interface
Slow count(*) workaround	Fix optimiser filter overestimate	Lateral derived query tables supported
Table fragmentation	Indexes and queries	Outer query table ref support
Update InnoDB statistics	Optimisation cheatsheet	8.0.16 changes
Manage InnoDB file size	Optimisation tips from Jay Pipes	Check Constraint
Reclaim disk space	Optimise Load Data Infile	Large transactions
When ibdata1 grows fast	optimizer_switch flags	Mysql_upgrade going away
Redo log	optimizer_trace	8.0.17 changes
Configure redo log	Programmatically kill runaway query	Change summary
Archive redo log	Queues and Skip_Locked	Parallel import
New lock-free redo log	Read efficiency ratio is nonsense	8.0.18 changes
Transactions	Sargability	What's new
Transaction basics	Scalability	HA replication enhancements
Commit size	Sharding queries	Hash Join
DDL in transactions	Temp tables and disk space	Hash Join, Explain Analyze
Gap & next-key locks	Using optimiser hints	8.0.20 changes
InnoDB handling of transaction errors	Using the query_cache	Enforce PK constraints on slave
InnoDB transaction model	View performance	What's new
Investigate metadata locks	Views and indexes	Upgrade
Isolation level	What is read_rnd_buffer_size?	Upgrade and error logging improvements
List long transactions	DB design and performance	Upgrade checker
List uncommitted transactions	Delete, partitions and chunking	Upgrade to 8.0
Metadata lock issues in 5.5	InnoDB vs MyISAM	MySQL vs MariaDB
MVCC and history	Optimise GUIDs/UUIDs	MySQL 5-8 vs MariaDB 5 -10
Physical details	Explain	MySQL 5.6 vs MariaDB 10.0
Skip Locked	Explain basics	Views
Writing data	Explain Analyze	View algorithm
XA transaction improvements in 5.7	I/O Capacity	View performance

MySQL Articles, Snippets, Tips

See also our Common Queries page