MySQL Tips, Snippets & Articles

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

MySQL Articles, Tips & Snippets

See also our Common Queries page