MySQL Tips, Snippets & Articles

MySQL installation & setup	Configuration settings	Cache class
Installation methods & issues	Calculate InnoDB log file size	Just-in-time DataGridView
Licencing	Large page support	Example caller
Move MySQL to another *Nix disk	Track buffer pool use	Setup MySQL test app
Online MySQL config wizard	Tweak the InnoDB cache	Create a project
Reset 'root' password	and FullText	Add a reference to MySQL
Upgrading MySQL	and lower_case_table_names	Set up the form
Versions vs Operating Systems	Bulk Inserts and Load Data Infile	Write a MySQL connection class
What config file is MySQL using?	Changing tables from MyISAM to InnoDB	TreeViews
Windows	How to shrink or purge ibdata	Basic TreeView
Fix for 5.6.x Windows installer bug	InnoDB vs MyISAM	Stored procedures and Connector.NET
Install in Windows	InnoDB vs TokuDB benchmarks	Call stored procedure
Install in Windows Vista	InnoDB: Caveat usor	OUT parameter test case
Installation of Windows service fails with Error 0	Insert ... Select	MySQL Comparisons
Installer offers no new installations	Optimise Inserts	MySQL vs Oracle
Manual installation in Windows	Optimise updates	MySQL vs Oracle, from Oracle
MySQL won't start in Windows	Optimising MySQL & InnoDB performance **	MySQL vs PostgreSQL
MysqlInstanceConfig failure	Recover from InnoDB corruption	MySQL errors
Run mysql commandline client in Windows	Separate table for long column values?	Data validation using SIGNAL in Triggers
When the MySQL GUI installer fails **	Table does not exist	How to raise an error in MySQL
Android web server with MySQL	Table exists yet cannot be accessed	MySQL client errors
Build MySQL in Windows	Table fragmentation	MySQL server errors
Change MySQL data directory in *Nix	Workaround for slow count(*) in InnoDB	Server will not start (Windows)
Link MySQL, SQL Server	Transactions	Using SIGNAL in MySQL 5.5
Access & connection issues	Transaction basics	Optimisation, performance
Access denied errors	DDL in transactions	Optimisation cheatsheet **
Access MySQL on another workstation	Deadlock prevention and handling	Primer on index design for queries
Connect to MySQL from Base	InnoDB handling of transaction errors	17 config settings
Connect to MySQL from Excel	InnoDB transaction model	50 MySQL performance tips
Connect to MySQL server on a network	Investigate 'Waiting for table metadata lock'	Compare InnoDB, MySAM query performance
Connect to MySQL via SSH	Metadata lock problems	Fix optimiser overestimate of filter effect
Connect to MySQL with SSL	XA transaction improvements in 5.7	Indexes and queries
Connection issues on networks with Windows	Java	Optimisation slideshow
GUI MySQL connection through a firewall	Intro tutorial	Optimisation tips from Jay Pipes
Lost MySQL password in Windows	Set up Java for MySQL	Optimise Load Data Infile
SSH basics	Manipulating resultsets	Optimise rank data **
SSH tunnel to MySQL Server	Alternative to Order By Rand()	optimizer_trace
WorkBench	Export a query result to a file	Performance_schema
Remotely administer MySQL on Amazon EC2	Order By RAND()	Read efficiency ratio is nonsense
Remotely administer MySQL on W2K	MariaDB	Scalability
WB connection with remote host	Comparison with MySQL	Using optimiser hints
Windows connection to remote MySQL server	Compatibility with MySQL	Using the query_cache
Charset issues	ISPs that support MariaDB	View performance
Character set and collation basics	Features of interest	Views and indexes
ANSI character set	Multi-source replication	What is read_rnd_buffer_size?
Change column charset	PCRE regular expressions	DB design and performance
Delete special characters from text	Report Alter Table and Load Data progress	Delete, partitions and chunking
Euro symbol	Roles	InnoDB vs MyISAM
Get out of MySQL character set hell	Virtual columns	Optimise GUIDs/UUIDs
Switching to utf8mb4	MariaDB storage engines	InnoDB
UTF8 collations	Cassandra engine	InnoDB query optimisation basics
Windows and character sets	Connect engine	InnoDB_buffer_pool_size
WAMP	FederatedX engine	Optimise InnoDB table counts
WAMP download & installation	Open Query Graph engine	Query optimisation
Add Perl to WAMP	Sequence engine	How MySQL chooses between indexes
Database design	Sphinx engine	If your query is sluggish, look at the keys
Database basics	Spider engine	Indexing basics and queries
Basics	TokuDB engine	Investigate slow queries
What is a database?	XtraDB engine (InnoDB replacement)	Multi-range read optimisation explained
Basics of designing a database	Migration	Nested loop join
Build a database from use case analysis	Basic database export script	Optimise a range condition with Order By
Data modelling 101	Export table to CSV	Optimise BETWEEN queries
Foreign key: On Delete Set Null	Open source migration tools	Optimise geospatial queries
Many:many basics	Oracle to MySQL	Optimise Group By
MyISAM concurrency	Recompute auto_increment values on data export	Optimise MyISAM queries
MySql is a better NoSql	Miscellaneous	Optimise Order By with Limit
When and how to project child rows	Batch scripts	Optimise query cache efficiency
Alter engine in one or all tables	Coding style and standards	Optimiser cost model
Changing from MyISAM to InnoDB	Displaying images in web pages	Percona query analyser
Database and table size size limits	Format queries	Simplify a query to improve performance
Database templates with MySQL	Import fixed-width data	Optimise subqueries
Database tutorials on the web	Import large SQL script to remote DB	Subquery optimisation basics
Designing for query speed	Load_File() and AppArmor	Derived table instead of a subquery
Managing permissions	MySQL documentation lookup	In() vs Exists() before 5.6 and since
MySQL and scaling	MySQL downloads	Materialise the subquery: months to secs
MySQL limits	PREPARE an INSERT containing HTML	Queries
Normalisation: fine points	Run external command	Common MySQL queries
OLAP with MySQL	SQL formatter	Basics of PREPARE
Point-in-time architecture & audit trails	Version history graph	Divide and conquer
Round-robin database	MySQL and web apps	Emulate Common Table Expressions
Using an updatable view as a constraint	Comparison of HTML editors	Emulate materialised views
Why not to use bit columns in MySQL	MySQL and Java	FlexViews
Workarounds for absence of CHECK CONSTRAINT	Basics	Prepare a list of values
Managing duplicates	Get started with Java	Return query result as XML
Consolidate duplicates	Java intro	Replication
Find & delete rows with multiple IDs for same info	Tomcat, Eclipse and MySQL	and upgrades
Partitioning tables	Eclipse: an elegant IDE for MySQL	Async and group replication
Partitioning tips	NetBeans 6.5.1: not ready for prime time	Booking.com: automation disaster
When to partition	Oracle SQL Explorer	Encrypted replication
Special designs	MySQL and PHP	Group replication
Design for latitude/longitude search	Setup	Multi-master replication
FIFO queue	Configure MySQL and PHP on the Mac	Multi-master with N read slaves
Multi-terabyte databases	PDO vs. mysqli	Useful articles
Entity-attribute-value (EAV) model	PHP include files and database-enabled webapps	Reports
EAV designs and queries	Configure MySQL & PHP in Windows	Column header for a week
Administering MySQL	Apache PHP, MySQL & PhpMyAdmin	Open source report writers: comparison
Add auto_increment primary key to existing table	DreamWeaver, PHP & MySQL	Security
Automate copying of a database	Other configuration walkthroughs	Prevent SQL injection attacks
Automatically kill runaway queries	PHP and MySQL in Windows	RBAC kernel
Basic database synchronisation	WAMP installation packages	Security in the real world
Compare two MySQL configurations	Basics	Table encryption
Estimate data sizes by engine	MySQL-PHP basics from Dev Shed	Stored routines
Fix corrupted grants table	Call a stored procedure from PHP	Call stored procedure with OUT parameter
Fix corrupted grants table	Callback	Debugging stored routines
Information_schema	Error reporting	Documentation on error handling
Innotop: Monitor InnoDB	Master-detail data	Excellent tutorial on stored routines
Log MySQL logins	Multi-queries	How to build an IN() list for a stored procedure
Mass deletions	Pagination	How to call a stored procedure from PHP
Memory usage snapshot	Populate a dropdown in PHP: example	How to debug MySQL stored routines
Optimise MySQL memory usage	Populate one dropdown from another **	Nest cursors
Performance_schema	Query retrieval and display	Slowness of MySQL stored procedures
Perl script to compare database structures	Session setup	Sprocs if necessary, but not necessarily sprocs
Recover database from hard drive	MySQL, PHP character sets and collations	Workaround for absence of dynamic cursors
Rename Table and user privileges	PHP-MySQL charset basics	Strings
Server crashes	Escaping text	Parse a string into columns non-procedurally
Sys schema	Basic PDO example	Replace first instance only of a substring
Using the McAfee audit plugin	Dynamic dropdowns with Ajax	Search and replace in strings
Windows cmd line and MySQL	Insert PHP array into MySQL table	Word count
Backup & recovery	Jobs that run a long time	Triggers
Basic backup strategy	List child tables	Manage INSERT failures
Fast InnoDB snapshot	MySQL, PHP and SSL	Retrieve next auto_increment value in BEFORE Trigger
Incremental recovery	PEAR: usage notes	Update table B on Insert into table A
InnoDB backup methods	Prevent SQL injection attacks	Use a Trigger to block an Insert
InnoDB recovery	register_globals	Version notes
MySQL dump script	Show resultset as a bar graph	Backward-incompatible changes, 5.0 through 5.6
MySQLdump and consistency	Validate email address input	8.0
Restore databases via file copy	phpMyAdmin	Install 8.0.1 on Linux
Speed up dump import	Install	8.0 Milestone release features
Advanced	Create a stored procedure in phpMyAdmin	8.0 Auto_increment fixed
Connect to MySQL from Oracle	Snippets	8.0 First impressions
Connect to MySQL from SQL Server	Connect using object oriented syntax: examples	8.0 General Tablespaces: no FRM files
Matrix math in SQL	Dynamic variable names	8.0 Information_schema
MySQL Fabric	Generate query for names linked to attributes	8.0 JSON
MySQL internals	Snippet plugin for MySQL Workbench	8.0 Manual
Q&A for DBAs	Utility	8.0 Millions of tables **
Requests for MySQL 5.8	Create Table script	8.0 New reserved words **
Data warehouses	MySQL and Windows	8.0 optimisation improvements
Data mart or data warehouse?	Getting started with MySQL under Windows	8.0 Transactional data dictionary
Dimensional modelling	Connect to MySQL from Excel	8.0 Unified data dictionary
Improve MySQL data warehouse performance	Connect to MySQL from Microsoft SQL Server	Migrate from older collations
MySQL data warehouse	Load Data Infile in Windows	8.0 Charsets & collations
MySQL data warehouse slideshow	MySQL and Microsoft Access	8.0 New collations
MySQL data warehouse--essays	ODBC and last_insert_id()	8.0 Unicode in MySQL
Summary tables	The notorious Windows backslash	8.0 UTF8 Improvements
Date & Time	VB/ADO connection string	Accents and case
Epochs	MySQL and Visual Studio 2005	Collation slideshow **
Fiscal week of a date	Call a stored procedure: snippet	8.0 Common table expressions
Fractional seconds	Dynamically bind MySqlData.dll: snippet	CTE slideshow
Milliseconds	Basic query methods	CTEs 1. Introduction
Time zone conversion	Assemble query strings	CTEs 2. Series
Documentation	Determine if a column value needs to be quoted	CTEs 3. Hierarchies
Indexed topics	Execute a query, return a DataTable	CTEs 4. Cycles in hierarchies
Feature summaries by version	Retrieve a scalar result	8.0 Group replication
GUI Tools	Execute a non-SELECT command	Synchronous or not?
OS-based MySQL GUIs	Prepared statements	8.0 Roles
HeidiSQL	DataGridView	Basic syntax
MySQL Query Browser	Find the current row after a sort: snippet	Intro by Giuseppe Maxia
Multi-query scripts in Query Browser	DataGridView: column search	White papers
Web-based MySQL GUIs	Simple Column Find dialog	8.0 What's new
phpMyAdmin	DgvRowFinder class	Descending indexes
TheUsual	DgvRowFinder example caller	Faster batch row retrieval
InnoDB	DataSets	Invisible indexes
InnoDB architecture	Multi-key relations	Views
B+Tree index structures in InnoDB	Just-in-time lookup grid	View performance
Internal ibdata structure	Overview
InnoDB config variables	DataRetriever class

MySQL Articles, Tips & Snippets

See also our Common Queries page