MySQL Tips, Snippets & Articles

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

MySQL Articles, Tips & Snippets

See also our Common Queries page