MySQL Tips, Snippets & Articles

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

MySQL Articles, Tips & Snippets

See also our Common Queries page