MySQL Tips, Snippets & Articles

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

MySQL Articles, Tips & Snippets

See also our Common Queries page