### Table Creation for Examples

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch10_Optimization.md

Schema setup for the tables used in the provided hash join examples.

```sql
CREATE TABLE t1 (c1 INT, c2 INT);
CREATE TABLE t2 (c1 INT, c2 INT);
CREATE TABLE t3 (c1 INT, c2 INT);
```

--------------------------------

### Create Partitioned Table and Populate Data

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch26_Partitioning.md

Example setup for a range-partitioned table and initial data insertion.

```sql
CREATE TABLE e (
 id INT NOT NULL,
 fname VARCHAR(30),
 lname VARCHAR(30)
)
 PARTITION BY RANGE (id) (
 PARTITION p0 VALUES LESS THAN (50),
 PARTITION p1 VALUES LESS THAN (100),
 PARTITION p2 VALUES LESS THAN (150),
 PARTITION p3 VALUES LESS THAN (MAXVALUE)
);
INSERT INTO e VALUES
 (1669, "Jim", "Smith"),
 (337, "Mary", "Jones"),
 (16, "Frank", "White"),
 (2005, "Linda", "Black");
```

--------------------------------

### Install a Storage Engine Plugin

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch18_Alternative_Storage_Engines.md

Shows how to install a storage engine plugin, such as the EXAMPLE engine, using the INSTALL PLUGIN statement. The plugin file must be in the MySQL plugin directory, and the user needs INSERT privilege for the mysql.plugin table.

```sql
INSTALL PLUGIN example SONAME 'ha_example.so';
```

--------------------------------

### Create and populate partitioned table

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch26_Partitioning.md

Setup for the employees table used in partition selection examples.

```sql
SET @@SQL_MODE = '';
CREATE TABLE employees (
 id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 fname VARCHAR(25) NOT NULL,
 lname VARCHAR(25) NOT NULL,
 store_id INT NOT NULL,
 department_id INT NOT NULL
)
 PARTITION BY RANGE(id) (
 PARTITION p0 VALUES LESS THAN (5),
 PARTITION p1 VALUES LESS THAN (10),
 PARTITION p2 VALUES LESS THAN (15),
 PARTITION p3 VALUES LESS THAN MAXVALUE
);
INSERT INTO employees VALUES
 ('', 'Bob', 'Taylor', 3, 2), ('', 'Frank', 'Williams', 1, 2),
 ('', 'Ellen', 'Johnson', 3, 4), ('', 'Jim', 'Smith', 2, 4),
 ('', 'Mary', 'Jones', 1, 1), ('', 'Linda', 'Black', 2, 3),
 ('', 'Ed', 'Jones', 2, 1), ('', 'June', 'Wilson', 3, 1),
 ('', 'Andy', 'Smith', 1, 3), ('', 'Lou', 'Waters', 2, 4),
 ('', 'Jill', 'Stone', 1, 4), ('', 'Roger', 'White', 3, 2),
 ('', 'Howard', 'Andrews', 1, 2), ('', 'Fred', 'Goldberg', 3, 3),
 ('', 'Barbara', 'Brown', 2, 3), ('', 'Alice', 'Rogers', 2, 2),
 ('', 'Mark', 'Morgan', 3, 3), ('', 'Karen', 'Cole', 3, 2);
```

--------------------------------

### Install MySQL Workbench on Fedora

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch02_Installing_MySQL.md

Example command to install the MySQL Workbench community edition on Fedora using dnf.

```bash
$> sudo dnf install mysql-workbench-community
```

--------------------------------

### Setup Tables and Initial Data for Trigger Example

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch27_Stored_Objects.md

These SQL statements create the necessary tables and populate them with initial data to demonstrate the 'testref' trigger's functionality.

```sql
CREATE TABLE test1(a1 INT);
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(
 a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 b4 INT DEFAULT 0
);
```

```sql
INSERT INTO test3 (a3) VALUES
 (NULL), (NULL), (NULL), (NULL), (NULL),
 (NULL), (NULL), (NULL), (NULL), (NULL);
```

```sql
INSERT INTO test4 (a4) VALUES
 (0), (0), (0), (0), (0), (0), (0), (0), (0), (0);
```

--------------------------------

### Example Table Setup and Query for Skip Scan

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch10_Optimization.md

This SQL script sets up a sample table and inserts data, followed by an EXPLAIN query that could potentially utilize the Skip Scan access method.

```sql
CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL, PRIMARY KEY(f1, f2));
INSERT INTO t1 VALUES
 (1,1), (1,2), (1,3), (1,4), (1,5),
 (2,1), (2,2), (2,3), (2,4), (2,5);
INSERT INTO t1 SELECT f1, f2 + 5 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 10 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 20 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 40 FROM t1;
ANALYZE TABLE t1;
EXPLAIN SELECT f1, f2 FROM t1 WHERE f2 > 40;
```

--------------------------------

### Standard MySQL Source Installation Sequence

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch02_Installing_MySQL.md

A complete workflow for installing MySQL from a compressed tar file, including user setup, compilation, and initialization.

```bash
# Preconfiguration setup
$> groupadd mysql
$> useradd -r -g mysql -s /bin/false mysql
# Beginning of source-build specific instructions
$> tar zxvf mysql-VERSION.tar.gz
$> cd mysql-VERSION
$> mkdir bld
$> cd bld
$> cmake ..
$> make
$> make install
# End of source-build specific instructions
# Postinstallation setup
$> cd /usr/local/mysql
$> mkdir mysql-files
$> chown mysql:mysql mysql-files
$> chmod 750 mysql-files
$> bin/mysqld --initialize --user=mysql
$> bin/mysqld_safe --user=mysql &
# Next command is optional
$> cp support-files/mysql.server /etc/init.d/mysql.server
```

--------------------------------

### Install MySQL Server

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch02_Installing_MySQL.md

Installs the MySQL server, client, and common database files.

```bash
$> sudo apt-get install mysql-server
```

--------------------------------

### Install MySQL as a manual service

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch02_Installing_MySQL.md

Use this command to install the MySQL server as a service that does not start automatically during the boot process.

```shell
C:\> "C:\Program Files\MySQL\MySQL Server 9.6\bin\mysqld" --install-manual
```

--------------------------------

### Create and Query EXAMPLE Storage Engine Table

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch18_Alternative_Storage_Engines.md

Demonstrates creating a table with the EXAMPLE storage engine, attempting to insert data (which fails), and querying the empty table. The EXAMPLE engine is a stub and does not support data storage.

```sql
mysql> CREATE TABLE test (i INT) ENGINE = EXAMPLE;
Query OK, 0 rows affected (0.78 sec)
mysql> INSERT INTO test VALUES ROW(1), ROW(2), ROW(3);
ERROR 1031 (HY000): Table storage engine for 'test' doesn't »
mysql> SELECT * FROM test;
Empty set (0.31 sec)
```

--------------------------------

### Install MySQL Distribution on Unix

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch02_Installing_MySQL.md

Use 'make install' to install the MySQL distribution to the configured directory. Run as root if necessary. Specify DESTDIR for custom installation paths.

```bash
$> make install

```

```bash
$> make install DESTDIR="/opt/mysql"

```

--------------------------------

### Setup Test Tables and Data

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch10_Optimization.md

Initial setup for testing subquery optimization with two tables.

```sql
mysql> DROP TABLE IF EXISTS t1, t2;
mysql> CREATE TABLE t1 (a INT, b INT);
mysql> CREATE TABLE t2 (a INT, b INT);
mysql> INSERT INTO t1 VALUES ROW(1,10), ROW(2,20), ROW(3,30);
```

```sql
mysql> INSERT INTO t2
 -> VALUES ROW(1,10), ROW(2,20), ROW(3,30), ROW(1,110), ROW(2,120), ROW(3,130);
mysql> SELECT * FROM t1
 -> WHERE t1.b < 0
 -> OR
 -> t1.a IN (SELECT t2.a + 1 FROM t2);
```

--------------------------------

### Start MySQL Server

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch03_Upgrading_MySQL.md

Start the upgraded MySQL server using the existing data directory.

```bash
mysqld_safe --user=mysql --datadir=/path/to/existing-datadir &
```

--------------------------------

### Install libaio Library

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch02_Installing_MySQL.md

Install the required libaio1 library if it is missing from the system.

```bash
sudo apt-get install libaio1
```

--------------------------------

### Navigate to Installation Directory

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch02_Installing_MySQL.md

Change the current working directory to the target installation path.

```bash
$> cd /usr/local
```

--------------------------------

### Example X Plugin Status Output

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch22_Using_MySQL_as_a_Document_Store.md

This is an example output from 'SHOW plugins' indicating that the mysqlx plugin is active.

```text
+----------------------------+----------+--------------------+---------+---------+
| Name | Status | Type | Library | License |
+----------------------------+----------+--------------------+---------+---------+
...
| mysqlx | ACTIVE | DAEMON | NULL | GPL |
...
+----------------------------+----------+--------------------+---------+---------+
```

--------------------------------

### Install MySQL as a Windows Service

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch02_Installing_MySQL.md

Use this command to register the MySQL server as a Windows service. Note that this command does not start the server.

```batch
C:\> "C:\Program Files\MySQL\MySQL Server 9.6\bin\mysqld" --install
```

--------------------------------

### Start MySQL Server

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch02_Installing_MySQL.md

Use this command to start the MySQL server service. Ensure the system is systemd enabled.

```bash
$> systemctl start mysqld
```

--------------------------------

### Example Filesort Optimization with ORDER BY and LIMIT

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch10_Optimization.md

These examples demonstrate queries that can benefit from in-memory filesort operations when combined with ORDER BY and LIMIT.

```sql
SELECT col1, ... FROM t1 ... ORDER BY name LIMIT 10;
```

```sql
SELECT col1, ... FROM t1 ... ORDER BY RAND() LIMIT 15;
```

--------------------------------

### Start MySQL 9.6 Server

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch03_Upgrading_MySQL.md

Starts the MySQL 9.6 server using the newly initialized data directory. Run in the background.

```bash
mysqld_safe --user=mysql --datadir=/path/to/9.6-datadir &
```

--------------------------------

### Configure New MySQL Instances

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch02_Installing_MySQL.md

Examples for setting up new MySQL instances with varying levels of complexity and user definitions.

```bash
# Simple 
mysql_configurator.exe --console --action=configure --password=test
# More complex
mysql_configurator.exe --console --action=configure --password=test --port=3320 --enable-pipe-names --pipe-name=MYSQL_PIPE --server-id=2 --install-sample-database=both
# More complex, also with users
mysql_configurator.exe --console --action=configure --password=other123 --add-user='john':'pa$$':"Db Admin":MYSQL --add-user='mary':'p4ssW0rd':"Administrator":MYSQL
```

--------------------------------

### Install Modules via PPM on Windows

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch02_Installing_MySQL.md

Commands to start the PPM program and install DBI and DBD-mysql modules.

```shell
C:\> C:\perl\bin\ppm.pl
```

```shell
ppm> install DBI
```

```shell
ppm> install DBD-mysql
```

--------------------------------

### Setup Relational Tables and Data

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch27_Stored_Objects.md

Create the base tables and populate them with sample data for use in JSON duality views.

```sql
mysql> CREATE TABLE customers (
 customer_id INT PRIMARY KEY,
 name VARCHAR(100)
);
mysql> CREATE TABLE orders (
 order_id INT PRIMARY KEY,
 customer_id INT,
 product VARCHAR(100),
 amount DECIMAL(10,2),
 FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
mysql> INSERT INTO customers VALUES (1, "Alice"), (2, "Bob");
Query OK, 2 rows affected (0.009 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> INSERT INTO orders VALUES (1, 1, "Milk", 10), (2, 1, "Curd", 5), (3, 2, "Flour", 20), (4, 2, "Biscuits", 5);
Query OK, 4 rows affected (0.007 sec)
Records: 4 Duplicates: 0 Warnings: 0
```

--------------------------------

### Optimizer Trace Example Header

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch10_Optimization.md

A comment header indicating the start of an optimizer trace example for ORDER BY and GROUP BY simplification.

```text
#
# Tracing of ORDER BY & GROUP BY simplification.
```

--------------------------------

### Resolve Package Conflicts During Installation

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch02_Installing_MySQL.md

This example shows a typical conflict resolution prompt during MySQL installation via Zypper. Choose the 'replacement' option to proceed with installing packages from the MySQL SLES repository.

```bash
Problem: mysql-community-server-5.6.22-2.sles11.x86_64 requires mysql-community-client = 5.6.22-2.sles11,
 but this requirement cannot be provided uninstallable providers:
 mysql-community-client-5.6.22-2.sles11.x86_64[mysql56-community]
Solution 1: replacement of mysql-client-5.5.31-0.7.10.x86_64 with mysql-community-client-5.6.22-2.sles11.x86_64
Solution 2: do not install mysql-community-server-5.6.22-2.sles11.x86_64
Solution 3: break mysql-community-server-5.6.22-2.sles11.x86_64 by ignoring some of its dependencies
Choose from above solutions by number or cancel [1/2/3/c] (c)
```

--------------------------------

### Example output of mysqladmin version

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch02_Installing_MySQL.md

Sample output showing server version, uptime, and connection details.

```text
$> bin/mysqladmin version
mysqladmin Ver 14.12 Distrib 9.6.0, for pc-linux-gnu on i686
...
Server version 9.6.0
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 14 days 5 hours 5 min 21 sec
Threads: 1 Questions: 366 Slow queries: 0
Opens: 0 Flush tables: 1 Open tables: 19
Queries per second avg: 0.000
```

--------------------------------

### Start MySQL Server Manually

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch02_Installing_MySQL.md

This command starts the MySQL server without the --console option, meaning diagnostic output will be directed to the error log file. Ensure the path to 'mysqld' is correct for your installation.

```bash
C:\> "C:\Program Files\MySQL\MySQL Server 9.6\bin\mysqld"
```

--------------------------------

### Queries Not Utilizing the Composite Index

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch10_Optimization.md

Examples of queries that fail to use the name index because they do not start with the leftmost prefix.

```sql
SELECT * FROM test WHERE first_name='John';
SELECT * FROM test
 WHERE last_name='Jones' OR first_name='John';
```

--------------------------------

### Install and initialize MySQL binary distribution

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch02_Installing_MySQL.md

Standard sequence of commands to create the MySQL user, unpack the binary, set up the data directory, and initialize the server.

```bash
$> groupadd mysql
$> useradd -r -g mysql -s /bin/false mysql
$> cd /usr/local
$> tar xvf /path/to/mysql-VERSION-OS.tar.xz
$> ln -s full-path-to-mysql-VERSION-OS mysql
$> cd mysql
$> mkdir mysql-files
$> chown mysql:mysql mysql-files
$> chmod 750 mysql-files
$> bin/mysqld --initialize --user=mysql
$> bin/mysqld_safe --user=mysql &
# Next command is optional
$> cp support-files/mysql.server /etc/init.d/mysql.server
```

--------------------------------

### Get Detailed Help for a MySQL Shell Command

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch22_Using_MySQL_as_a_Document_Store.md

To get specific help on a particular MySQL Shell command, append the command name to the \help command. For example, \help \connect provides details about the connect command.

```mysqlsh
mysql-py> \help \connect
```

--------------------------------

### Example of JOIN_ORDER and JOIN_PREFIX Hints

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch10_Optimization.md

Demonstrates the use of JOIN_ORDER and JOIN_PREFIX hints. The JOIN_PREFIX hint is ignored if it conflicts with table dependencies.

```sql
/*+ JOIN_ORDER(t1, t2) JOIN_PREFIX(t2, t1) */
```

--------------------------------

### Tight Index Scan with Constant Prefix

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch10_Optimization.md

Example of a query using a Tight Index Scan where the GROUP BY clause does not start with the first key part, but a constant condition is provided for that part.

```sql
SELECT c1, c2, c3 FROM t1 WHERE c1 = 'a' GROUP BY c2, c3;
```

--------------------------------

### Create and Populate a Partitioned Table

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch26_Partitioning.md

Sets up a table partitioned by range on the id column and inserts sample data.

```sql
CREATE TABLE trb1 (id INT, name VARCHAR(50), purchased DATE)
 PARTITION BY RANGE(id)
 (
 PARTITION p0 VALUES LESS THAN (3),
 PARTITION p1 VALUES LESS THAN (7),
 PARTITION p2 VALUES LESS THAN (9),
 PARTITION p3 VALUES LESS THAN (11)
 );
INSERT INTO trb1 VALUES
 (1, 'desk organiser', '2003-10-15'),
 (2, 'CD player', '1993-11-05'),
 (3, 'TV set', '1996-03-10'),
 (4, 'bookcase', '1982-01-10'),
 (5, 'exercise bike', '2004-05-09'),
 (6, 'sofa', '1987-06-05'),
 (7, 'popcorn maker', '2001-11-22'),
 (8, 'aquarium', '1992-08-04'),
 (9, 'study desk', '1984-09-16'),
 (10, 'lava lamp', '1998-12-25');
```

--------------------------------

### Build MySQL Server from Source

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch02_Installing_MySQL.md

After cloning and checking out a branch, you can build MySQL Server from source. Refer to the standard source distribution installation guide, skipping the download and unpacking steps.

```bash
make test
```

--------------------------------

### Examine Partitioning with Primary Keys

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch26_Partitioning.md

Demonstrates EXPLAIN output after adding a primary key to the partitioned table.

```sql
mysql> ALTER TABLE trb1 ADD PRIMARY KEY (id);
Query OK, 10 rows affected (0.03 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> EXPLAIN SELECT * FROM trb1 WHERE id < 5\G
```

--------------------------------

### Case-Sensitive Match for Names Beginning with 'b' using REGEXP_LIKE()

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch05_Tutorial.md

To perform a case-sensitive match, use the BINARY keyword or specify the 'c' match-control character with REGEXP_LIKE(). This example finds names starting with a lowercase 'b'.

```sql
SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b', 'c');
```

--------------------------------

### Run Initialization Scripts

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch02_Installing_MySQL.md

Mount a directory containing .sh or .sql scripts to /docker-entrypoint-initdb.d/ to execute them automatically upon container creation.

```bash
docker run --name=mysql1 \
--mount type=bind,src=/path-on-host-machine/scripts/,dst=/docker-entrypoint-initdb.d/ \
-d container-registry.oracle.com/mysql/community-server:tag
```

--------------------------------

### Display MySQL Server Options

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch02_Installing_MySQL.md

Use this command to view all supported options for the 'mysqld' executable. This is helpful for understanding available configurations and troubleshooting startup problems.

```bash
mysqld --verbose --help
```

--------------------------------

### Navigate to MySQL Installation Directory

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch02_Installing_MySQL.md

Commands to change the current directory to the MySQL installation path for MSI or ZIP archive installations.

```batch
C:\> cd "C:\Program Files\MySQL\MySQL Server 9.6"
```

```batch
C:\> cd C:\mysql
```

--------------------------------

### Start MySQL Service

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch02_Installing_MySQL.md

Use these commands to start the MySQL service and view any error messages if the server fails to start. Replace 'mysqld_service_name' with your actual service name.

```batch
SC START mysqld_service_name
```

```batch
NET START mysqld_service_name
```

--------------------------------

### Create and Populate a Subpartitioned Table

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch26_Partitioning.md

Sets up a table partitioned by RANGE and subpartitioned by KEY, then populates it with sample data and creates a nonpartitioned copy.

```sql
mysql> CREATE TABLE es (
 -> id INT NOT NULL,
 -> fname VARCHAR(30),
 -> lname VARCHAR(30)
 -> )
 -> PARTITION BY RANGE (id)
 -> SUBPARTITION BY KEY (lname)
 -> SUBPARTITIONS 2 (
 -> PARTITION p0 VALUES LESS THAN (50),
 -> PARTITION p1 VALUES LESS THAN (100),
 -> PARTITION p2 VALUES LESS THAN (150),
 -> PARTITION p3 VALUES LESS THAN (MAXVALUE)
 -> );
Query OK, 0 rows affected (2.76 sec)
mysql> INSERT INTO es VALUES
 -> (1669, "Jim", "Smith"),
 -> (337, "Mary", "Jones"),
 -> (16, "Frank", "White"),
 -> (2005, "Linda", "Black");
Query OK, 4 rows affected (0.04 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> CREATE TABLE es2 LIKE es;
Query OK, 0 rows affected (1.27 sec)
mysql> ALTER TABLE es2 REMOVE PARTITIONING;
Query OK, 0 rows affected (0.70 sec)
Records: 0 Duplicates: 0 Warnings: 0
```

--------------------------------

### Install MySQL on Red Hat-based systems using yum

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch02_Installing_MySQL.md

Use the yum command to install MySQL server, client, and libraries. Ensure you specify all necessary packages for a complete installation.

```bash
#> yum install mysql mysql-server mysql-libs mysql-server
Loaded plugins: presto, refresh-packagekit
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package mysql.x86_64 0:5.1.48-2.fc13 set to be updated
---> Package mysql-libs.x86_64 0:5.1.48-2.fc13 set to be updated
---> Package mysql-server.x86_64 0:5.1.48-2.fc13 set to be updated
--> Processing Dependency: perl-DBD-MySQL for package: mysql-server-5.1.48-2.fc13.x86_64
--> Running transaction check
---> Package perl-DBD-MySQL.x86_64 0:4.017-1.fc13 set to be updated
--> Finished Dependency Resolution
Dependencies Resolved
================================================================================
 Package Arch Version Repository Size
================================================================================
Installing:
 mysql x86_64 5.1.48-2.fc13 updates 889 k
 mysql-libs x86_64 5.1.48-2.fc13 updates 1.2 M
 mysql-server x86_64 5.1.48-2.fc13 updates 8.1 M
Installing for dependencies:
 perl-DBD-MySQL x86_64 4.017-1.fc13 updates 136 k
Transaction Summary
================================================================================
Install 4 Package(s)
Upgrade 0 Package(s)
Total download size: 10 M
Installed size: 30 M
Is this ok [y/N]: y
```

```bash
Downloading Packages:
Setting up and reading Presto delta metadata
Processing delta metadata
Package(s) data still to download: 10 M
(1/4): mysql-5.1.48-2.fc13.x86_64.rpm | 889 kB 00:04
(2/4): mysql-libs-5.1.48-2.fc13.x86_64.rpm | 1.2 MB 00:06
(3/4): mysql-server-5.1.48-2.fc13.x86_64.rpm | 8.1 MB 00:40
(4/4): perl-DBD-MySQL-4.017-1.fc13.x86_64.rpm | 136 kB 00:00
--------------------------------------------------------------------------------
Total 201 kB/s | 10 MB 00:52
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
 Installing : mysql-libs-5.1.48-2.fc13.x86_64 1/4
 Installing : mysql-5.1.48-2.fc13.x86_64 2/4
 Installing : perl-DBD-MySQL-4.017-1.fc13.x86_64 3/4
 Installing : mysql-server-5.1.48-2.fc13.x86_64 4/4
Installed:
 mysql.x86_64 0:5.1.48-2.fc13 mysql-libs.x86_64 0:5.1.48-2.fc13
 mysql-server.x86_64 0:5.1.48-2.fc13
Dependency Installed:
 perl-DBD-MySQL.x86_64 0:4.017-1.fc13
Complete!
```

--------------------------------

### Create a Partitioned Table

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch28_INFORMATION_SCHEMA_Tables.md

Example of creating a table with a HASH partitioning scheme.

```sql
CREATE TABLE tp (
 c1 INT,
 c2 INT,
 c3 VARCHAR(25)
)
PARTITION BY HASH(c1 + c2)
PARTITIONS 4;
```

--------------------------------

### Silent MySQL Installation to Custom Directory

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch02_Installing_MySQL.md

This command installs MySQL Server silently to a custom directory using the INSTALLDIR option, overriding the default installation path. Execute as Administrator.

```bash
$> msiexec /i "C:\mysql\mysql-9.6-winx64.msi" /qn /lv "C:\mysql\install.log" INSTALLDIR="C:\mysql"
```

--------------------------------

### SQL Optimizer Hints Examples

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch10_Optimization.md

Demonstrates various SQL optimizer hints for query optimization, including INDEX_MERGE, MRR, and NO_RANGE_OPTIMIZATION.

```sql
SELECT /*+ INDEX_MERGE(t1 f3, PRIMARY) */ f2 FROM t1
 WHERE f1 = 'o' AND f2 = f3 AND f3 <= 4;
```

```sql
SELECT /*+ MRR(t1) */ * FROM t1 WHERE f2 <= 3 AND 3 <= f3;
```

```sql
SELECT /*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) */ f1
 FROM t3 WHERE f1 > 30 AND f1 < 33;
```

```sql
INSERT INTO t3(f1, f2, f3)
 (SELECT /*+ NO_ICP(t2) */ t2.f1, t2.f2, t2.f3 FROM t1,t2
 WHERE t1.f1=t2.f1 AND t2.f2 BETWEEN t1.f1
 AND t1.f2 AND t2.f2 + 1 >= t1.f1 + 1);
```

```sql
SELECT /*+ SKIP_SCAN(t1 PRIMARY) */ f1, f2
 FROM t1 WHERE f2 > 40;
```

--------------------------------

### Install MySQL Server

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch02_Installing_MySQL.md

Install the MySQL community server package and its dependencies.

```bash
$> sudo zypper install mysql-community-server
```

--------------------------------

### Viewing Windowing Execution Plan

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch10_Optimization.md

Shows how to use `EXPLAIN FORMAT=JSON` to retrieve extensive windowing execution plan information, as traditional `EXPLAIN` output is insufficient.

```sql
EXPLAIN FORMAT=JSON
```

--------------------------------

### Install core MySQL packages

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch02_Installing_MySQL.md

Installs the essential MySQL packages in the required order.

```bash
$> sudo dpkg -i mysql-{common,community-client,client,community-server,server}_*.deb
```

--------------------------------

### Start MySQL Service

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch02_Installing_MySQL.md

Commands to start the MySQL service on different Linux distributions.

```bash
$> systemctl start mysqld
```

```bash
$> systemctl start mysql
```

--------------------------------

### Create and Navigate to Build Directory (Internal)

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch02_Installing_MySQL.md

Alternatively, you can create a build directory (e.g., 'build') inside the top-level source directory and navigate into it.

```bash
$> mkdir build
$> cd build
```

--------------------------------

### Example CONNECTION String

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch18_Alternative_Storage_Engines.md

This is an example of a CONNECTION string format used for FEDERATED tables.

```sql
CONNECTION='mysql://fed_user@remote_host:9306/federated/test_table';
```

--------------------------------

### List CMake Configuration Options (Overview with Help)

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch02_Installing_MySQL.md

This command provides an overview of CMake configuration options along with brief help text for each option.

```bash
$> cmake .. -LH
```

--------------------------------

### Display Table Partitioning Schema

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch26_Partitioning.md

Use SHOW CREATE TABLE to inspect the current partitioning configuration of a table.

```sql
mysql> SHOW CREATE TABLE tr\G
*************************** 1. row ***************************
 Table: tr
Create Table: CREATE TABLE `tr` (
 `id` int(11) DEFAULT NULL,
 `name` varchar(50) DEFAULT NULL,
 `purchased` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE ( YEAR(purchased))
(PARTITION p0 VALUES LESS THAN (1990) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (2005) ENGINE = InnoDB,
```

```sql
 PARTITION p4 VALUES LESS THAN (2010) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (2015) ENGINE = InnoDB) */
1 row in set (0.00 sec)
```

--------------------------------

### Install MySQL on Debian-based systems using apt-get

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch02_Installing_MySQL.md

Use the apt-get command to install MySQL client and server packages. It is recommended to specify an explicit version, such as mysql-client-5.1, to ensure the desired version is installed.

```bash
apt-get install mysql-client mysql-server
```

--------------------------------

### Install MySQL NDB Cluster SQL Node

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch02_Installing_MySQL.md

Use this command to install the SQL node component for MySQL NDB Cluster. After installation, refer to the MySQL Server startup documentation for initialization steps.

```bash
$> sudo zypper install mysql-cluster-community-server
```

--------------------------------

### Example Key Tuples in Key Order

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch10_Optimization.md

Illustrates the order of key tuples in a multiple-part index, showing how NULL values are handled.

```text
key_part1 key_part2 key_part3
 NULL 1 'abc'
 NULL 1 'xyz'
 NULL 2 'foo'
 1 1 'abc'
 1 1 'xyz'
 1 2 'abc'
 2 1 'aaa'
```

--------------------------------

### Connect to MySQL Database

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch05_Tutorial.md

Start the command-line tool and select a specific database.

```bash
$> mysql your-database-name
```

--------------------------------

### Remove Old MySQL Installation

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch02_Installing_MySQL.md

When replacing a MySQL installation that was done using direct .deb package downloads, use this command to remove the old MySQL packages before installing from the MySQL APT repository.

```bash
$> sudo dpkg -P mysql
```

--------------------------------

### Install MySQL NDB Cluster Test Suite Dependencies

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch02_Installing_MySQL.md

When running the MySQL NDB Cluster test suite, ensure all necessary components are installed. Use this command to install the required packages via zypper.

```bash
zypper install mysql-cluster-community-auto-installer
mysql-cluster-community-management-server
mysql-cluster-community-data-node
mysql-cluster-community-memcached
mysql-cluster-community-java
mysql-cluster-community-ndbclient-devel
```

--------------------------------

### Navigate to MySQL Installation Directory

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch02_Installing_MySQL.md

Change the current working directory to the MySQL installation root.

```bash
cd /usr/local/mysql
```

--------------------------------

### Basic EXPLAIN Output Example

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch10_Optimization.md

This is a sample output from the EXPLAIN statement, showing join information for a query.

```text
table type possible_keys key key_len ref rows Extra
tt ALL AssignedPC NULL NULL NULL 3872 Using
 ClientID, where
 ActualPC
et eq_ref PRIMARY PRIMARY 15 tt.ActualPC 1
et_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1
do eq_ref PRIMARY PRIMARY 15 tt.ClientID 1
```

--------------------------------

### Install MySQL deb packages

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch02_Installing_MySQL.md

Installs specific MySQL component packages using dpkg.

```bash
$> sudo dpkg -i package-name.deb
```

--------------------------------

### Verifying MySQL Path Settings

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch02_Installing_MySQL.md

Check the effect of startup path options by running the server with verbose help flags, or query the current variables of a running server.

```bash
$> ./mysqld --basedir=/usr/local --verbose --help
```

```bash
$> mysqladmin variables
```

```bash
$> mysqladmin -h host_name variables
```

--------------------------------

### Install build dependencies

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch02_Installing_MySQL.md

Installs the necessary packages required to build the MySQL server from source.

```bash
$> sudo apt-get build-dep mysql-server
```

--------------------------------

### Sample Database Management

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch02_Installing_MySQL.md

Install or uninstall sample databases like Sakila and World. This is useful for testing and development purposes.

```APIDOC
## MANAGE sample_database

### Description
Installs or uninstalls specified sample databases. Available options include 'All', 'Sakila', 'World', and 'None'.

### Method
configure, reconfigure

### Endpoint
N/A

### Parameters
#### Query Parameters
- **install_sample_database** (list) - Optional - Specifies which sample databases to install. Values: All, Sakila, World, None. Default: none
- **uninstall_sample_database** (list) - Optional - Specifies which sample databases to uninstall. Values: All, Sakila, World, None. Default: none

### Request Example
```json
{
  "install_sample_database": "Sakila"
}
```

### Response
#### Success Response (200)
- **message** (string) - Sample database operation completed successfully.

#### Response Example
```json
{
  "message": "Sakila sample database installed successfully."
}
```
```

--------------------------------

### Install Shared Client Libraries

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch02_Installing_MySQL.md

Installs the libmysqlclient21 package for shared client library support.

```bash
$> sudo apt-get install libmysqlclient21
```

--------------------------------

### List databases with mysqlshow

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch02_Installing_MySQL.md

Display all available databases on the server.

```bash
$> bin/mysqlshow
+--------------------+
| Databases |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
```

--------------------------------

### INSTALL COMPONENT

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch11_Language_Structure.md

Installs the query_attributes component to enable the use of query attributes in SQL statements.

```APIDOC
## INSTALL COMPONENT "file://component_query_attributes"

### Description
Installs the query_attributes component, which registers it in the mysql.component system table for automatic loading on server startup.

### Method
SQL Statement

### Request Example
INSTALL COMPONENT "file://component_query_attributes";
```

--------------------------------

### Example of Multiple Join-Order Hints

Source: https://github.com/rosalesch/context-documenation/blob/main/mysql-9_6-Reference-manual/Ch10_Optimization.md

Demonstrates the application of JOIN_PREFIX, JOIN_ORDER, and JOIN_SUFFIX hints within a single SELECT statement, including hints applied to subqueries.

```sql
SELECT
/*+ JOIN_PREFIX(t2, t5@subq2, t4@subq1)
 JOIN_ORDER(t4@subq1, t3)
 JOIN_SUFFIX(t1) */
COUNT(*) FROM t1 JOIN t2 JOIN t3
 WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
 AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
```