MySQL Cheatsheet

MySQL Cheatsheet

=========================
Reset MySQL root password
=========================
# Reference: https://www.a2hosting.com/kb/developer-corner/mysql/reset-mysql-root-password/

================
MySQL Calculator
================
# Use this calculator to calculate how many max connections the MySQL instance can serve
# based on the provided RAM size
https://www.mysqlcalculator.com/

# MySQL Memory Allocation
https://mysql.rjweb.org/doc.php/memory

=======================
innodb_buffer_pool_size
=======================
https://scalegrid.io/blog/calculating-innodb-buffer-pool-size-for-your-mysql-server/

===============================
Dump all databases via terminal
===============================
mysqldump --single-transaction --quick --triggers --routines --events --add-drop-database --add-drop-table -u root -p --all-databases > all-databases.sql

=========================
Enable Slow Query Logging
=========================
---------------
VIA CONFIG FILE
---------------
// This approach requires restarting the service.

// For MariaDB
sudo vim /etc/mysql/mariadb.conf.d/50-server.cnf
// enable these 3:
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 0

// For MySQL
sudo vim /etc/mysql/my.cnf
// enable these 3:
slow-query-log = 1
slow-query-log-file = /var/log/mysql/mariadb-slow.log
long-query-time = 1

-----------
VIA QUERIES
-----------
// This approach does not require restarting the service.

-- General query log
SELECT @@general_log;
SELECT @@general_log_file;
SELECT @@log_output;
SET global general_log = 1;
SET global general_log_file = 'query.log';
SET global log_output = 'table';

-- Slow query log
SELECT @@slow_query_log;
SELECT @@slow_query_log_file;
SELECT @@long_query_time;
SELECT @@min_examined_row_limit;
SELECT @@log_queries_not_using_indexes;
-- this line will temporarily set the slow_query_log
SET session slow_query_log = 0;
-- this line will permanently set the slow_query_log
SET global slow_query_log = 0;
SET global slow_query_log_file = '/tmp/db05-slow.log';
SET global long_query_time = 10;

// MySQL - show slow queries for a specific database
// Run this via terminal
tail -f /tmp/db05-slow.log | grep -A 6 -E "db_name"

----------------------
MySQL graceful restart
----------------------
// Restart the MySQL service safely
// Can only be applied for a single instance of MySQL
// if you use replication, don't use this approach, as you'll need additional steps
https://dba.stackexchange.com/a/35414

SHOW VARIABLES LIKE 'innodb_fast_shutdown';
// Enable fast shutdown first
SET GLOBAL innodb_fast_shutdown = 1;

FLUSH LOCAL TABLES;
FLUSH TABLES WITH READ LOCK;

// from another ssh session do :
service mysql restart

-----------------
Get database size
-----------------
SELECT table_schema AS "Database", 
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)" 
FROM information_schema.TABLES 
GROUP BY table_schema;

----------------------
Get query cache status
----------------------
// reference : https://www.digitalocean.com/community/tutorials/how-to-optimize-mysql-with-query-cache-on-ubuntu-18-04
show variables like 'have_query_cache';
show variables like 'query_cache_%' ;
// query_cache_size = 16777216

------------------
Enable query cache
------------------
sudo vim /etc/mysql/mariadb.conf.d/50-server.cnf
// add these 3, under [mysqld]
query_cache_type=1
query_cache_size = 10M
query_cache_limit=256K

Or, via query:
// Check whether the query cache is supported in the database
SHOW VARIABLES like 'have_query_cache';

// Check query cache variables
SELECT @@query_cache_type;
SELECT @@query_cache_size;
SELECT @@query_cache_limit;

show variables like 'query_cache_%';

// This will not work, we have to enable the query cache via my.cnf
SET GLOBAL query_cache_type = 1;

// Set the query cache limit
SET GLOBAL query_cache_size = 50*1024*1024;
SET GLOBAL query_cache_limit = 1048576;

// To enable from AWS RDS, run this in the terminal
rds-modify-db-parameter-group testparamgroup \
--parameters="name=query_cache_size, value=10000000, method=immediate" \
--parameters="name=query_cache_limit, value=256000, method=immediate" \
--parameters="name=query_cache_type, value=1, method=immediate"

---------------
Query Profiling
---------------
Reference: https://www.digitalocean.com/community/tutorials/how-to-optimize-mysql-with-query-cache-on-ubuntu-18-04

This is an analysis service for monitoring the performance of MySQL queries

SET profiling = 1;
SELECT * FROM table_name;
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;

--------------
Get table size
--------------
SELECT
  TABLE_NAME AS `Table`,
  ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)`
FROM
  information_schema.TABLES
WHERE
  TABLE_SCHEMA = 'database_name' AND
  TABLE_NAME = 'table_name'
ORDER BY
  (DATA_LENGTH + INDEX_LENGTH)
DESC;

-----------------------------
Get table storage engine type
-----------------------------
SELECT TABLE_NAME,
       ENGINE
FROM   information_schema.TABLES
WHERE  TABLE_SCHEMA = 'database_name';

-----------------
Get list of views
-----------------
SELECT TABLE_SCHEMA, TABLE_NAME 
FROM information_schema.TABLES 
WHERE TABLE_TYPE LIKE 'VIEW' AND TABLE_SCHEMA LIKE 'database_name';

-----------
Show Status
-----------
SHOW STATUS

-----------------
Flush query cache
-----------------
RESET QUERY CACHE;

-------------------
Get query full info
-------------------
// Run this after executing a query
// it will display detailed information about the last executed query
show full processlist;

==============
TABLE INDEXING
==============
FORMAT:
    CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
        [index_type]
        ON tbl_name (key_part,...)
        [index_option]
        [algorithm_option | lock_option] ...
    key_part: {col_name [(length)] | (expr)} [ASC | DESC]
    index_option: {
        KEY_BLOCK_SIZE [=] value
      | index_type
      | WITH PARSER parser_name
      | COMMENT 'string'
      | {VISIBLE | INVISIBLE}
      | ENGINE_ATTRIBUTE [=] 'string'
      | SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
    }
    index_type:
        USING {BTREE | HASH}
    algorithm_option:
        ALGORITHM [=] {DEFAULT | INPLACE | COPY}
    lock_option:
        LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}

Examples:
    SHOW CREATE TABLE table_name;
    SHOW INDEX FROM table_name;
    ALTER TABLE table_name DROP INDEX index_name;
    ALTER TABLE table_name ADD INDEX index_name(column1);
    
-----------------
INDEX CARDINALITY
-----------------
Reference: https://www.mysqltutorial.org/mysql-index/mysql-index-cardinality/

Index cardinality refers to the uniqueness of values stored in a specified column within an index.

MySQL generates the index cardinality based on statistics stored as integers, therefore,
the value may not be necessarily exact.

The query optimizer uses the index cardinality to generate an optimal query plan for a given query. It also
uses the index cardinality to decide whether to use the index or not in the join operations.

If the query optimizer chooses the index with a low cardinality, it is may be
more effective than scan rows without using the index.

To view the index cardinality, you use the SHOW INDEXES command.
SHOW INDEXES FROM table_name;

------------
UNIQUE INDEX
------------
Reference: https://www.mysqltutorial.org/mysql-unique/
it allows you to enforce the uniqueness of values in one or more columns.

Unlike the PRIMARY KEY index, you can have more than one UNIQUE index per table.

Example:
	CREATE UNIQUE INDEX index_name
	ON table_name(index_column_1,index_column_2);

------------
PREFIX INDEX
------------
Reference: https://www.mysqltutorial.org/mysql-index/mysql-prefix-index/

When you create a secondary index for a column, MySQL stores the values of the columns in a separate data structure e.g., B-Tree and Hash.

In case the columns are the string columns, the index will consume a lot of disk space and potentially slow down the INSERT operations.

To address this issue, MySQL allows you to create an index for the leading part of the column values of the string columns using the following syntax:
column_name(length)

Example:
	CREATE INDEX index_name
	ON table_name(column_name(length));
In this syntax, the length is the number of characters for the non-binary string types such as CHAR, VARCHAR, and TEXT and
the number of bytes for binary string types e.g., BINARY, VARBINARY, and BLOB.

MySQL allows you to optionally create column prefix key parts for CHAR, VARCHAR, BINARY, and VARBINARY columns.
If you create indexes for BLOB and TEXT columns, you must specify the column prefix key parts.

Notice that the prefix support and lengths of prefixes if supported are storage engine dependent.
For InnoDB tables with  REDUNDANT or COMPACT row format, the maximum prefix length is 767 bytes.
However, for the InnoDB tables with  DYNAMIC or COMPRESSED row format,
the prefix length is 3,072 bytes. MyISAM tables have the prefix length up to 1,000 bytes.

Evaluate the uniqueness of the column:
   SELECT
      COUNT(DISTINCT LEFT(productName, 20)) unique_rows
   FROM
      products;
      
----------------
DESCENDING INDEX
----------------
Reference: https://www.mysqltutorial.org/mysql-index/mysql-descending-index/

A descending index is an index that stores key values in the descending order.

Example:
   CREATE TABLE t(
       a INT NOT NULL,
       b INT NOT NULL,
       INDEX a_asc_b_desc (a ASC, b DESC)
   );

---------------
COMPOSITE INDEX
---------------
Reference: https://www.mysqltutorial.org/mysql-index/mysql-composite-index/

A composite index is an index on multiple columns.

MySQL allows you to create a composite index that consists of up to 16 columns.

A composite index is also known as a multiple-column index.

The query optimizer uses the composite indexes for queries that test all columns in the index,
or queries that test the first columns, the first two columns, and so on.

---------------
CLUSTERED INDEX
---------------
Reference: https://www.mysqltutorial.org/mysql-index/mysql-clustered-index/

Typically, an index is a separate data structure such as B-Tree that stores the key values for faster lookups.

A clustered index, on the other hand, is the table. It is an index that enforces the ordering on the rows of the table physically.

Once a clustered index is created, all rows in the table will be stored according to the key columns used to create the clustered index.

Because a clustered index store the rows in sorted order, each table have only one clustered index.

-------------
SPATIAL INDEX
-------------
Reference: https://medium.com/sysf/playing-with-geometry-spatial-data-type-in-mysql-645b83880331

In the case of Spatial Data, MySQL uses SPATIAL INDEX which is for complex multi-dimensional data values and
it maintains this index in R-Tree tree structure.

A spatial index can only be created on a column with geometry type. It must be a NOT NULL column and
should contain data of only one SRID.

Examples:
    CREATE SPATIAL INDEX position_index ON table_name(Latitude, Longitude);
    ALTER TABLE table_name ADD SPATIAL INDEX(Latitude, Longitude);
---------
USE INDEX
---------
Reference: https://www.mysqltutorial.org/mysql-index/mysql-use-index/

Example:
   SELECT select_list
   FROM table_name USE INDEX(index_list)
   WHERE condition;

-----------
FORCE INDEX
-----------
Reference: https://www.mysqltutorial.org/mysql-index/mysql-force-index/

In case the query optimizer ignores the index, you can use the FORCE INDEX hint to instruct it to use the index instead.

Example:
   SELECT * 
   FROM table_name 
   FORCE INDEX (index_list)
   WHERE condition;