mysql-partitions-and-indexing.sql

#author          : Agung Pambudi
#email           : mail@agungpambudi.com
#version         : 0.1
#==============================================================================
#                                   _         _ _
# ___ ___ _ _ ___ ___ ___ ___ _____| |_ _ _ _| |_|  ___ ___ _____
#| .'| . | | |   | . | . | .'|     | . | | | . | |_|  _| . |     |
#|__,|_  |___|_|_|_  |  _|__,|_|_|_|___|___|___|_|_|___|___|_|_|_|
#    |___|       |___|_|


# Fungsi partition
# Do not scan partitions where there can be no matching values

# checking mysql support partition
SELECT
    PLUGIN_NAME as Name,
    PLUGIN_VERSION as Version,
    PLUGIN_STATUS as Status
    FROM INFORMATION_SCHEMA.PLUGINS
    WHERE PLUGIN_TYPE='STORAGE ENGINE';

SELECT partition_name, table_rows   
FROM information_schema.PARTITIONS   
WHERE table_name='bigtable'; 

SELECT partition_name, table_rows   
FROM information_schema.PARTITIONS   
WHERE table_name='bigtable';


# Partition Pruning : The optimization known as partition pruning is based on a relatively simple concept which can be described as “Do not scan partitions where there can be no matching values”. Suppose a partitioned table `bigtable` is created by this statement:


CREATE TABLE bigtable (
    fname VARCHAR(50) NOT NULL,
    lname VARCHAR(50) NOT NULL,
    region_code TINYINT UNSIGNED NOT NULL,
    dob DATE NOT NULL
)
PARTITION BY RANGE( YEAR(dob) ) (
    PARTITION d0 VALUES LESS THAN (1970),
    PARTITION d1 VALUES LESS THAN (1975),
    PARTITION d2 VALUES LESS THAN (1980),
    PARTITION d3 VALUES LESS THAN (1985),
    PARTITION d4 VALUES LESS THAN (1990),
    PARTITION d5 VALUES LESS THAN (2000),
    PARTITION d6 VALUES LESS THAN (2005),
    PARTITION d7 VALUES LESS THAN MAXVALUE
);

EXPLAIN SELECT * FROM bigtable WHERE dob = '2001-05-03' AND fname = 'Agung';


# memahami query cache
# ketika MySQL mengeksekusi suatu query, MySQL akan menyimpan Query (hanya perintah SELECT) beserta hasilnya di cache. Jika kemudian ada perintah SQL yang sama persis (identik), maka server akan mengambilnya dari cache tanpa perlu mengeksekusi query tersebut, hal ini akan membuat data hasil query dapat di peroleh dengan cepat.
# mengerti dan memahami query cache in dapat memberikan pemahaman bagi kita bagaimana menjalankan query sehingga query tersebut dapat dicache.

# compare

# no cache
SELECT SQL_NO_CACHE COUNT(*) FROM database.measures WHERE measure_timestamp >= '2016-01-01' AND DAYOFWEEK(measure_timestamp) = 1;   

# with cache
SELECT COUNT(*) FROM database.measures WHERE measure_timestamp >= '2016-01-01' AND DAYOFWEEK(measure_timestamp) = 1;   

# no cache
SELECT SQL_NO_CACHE COUNT(*) FROM database.partitioned_measures WHERE measure_timestamp >= '2016-01-01' AND DAYOFWEEK(measure_timestamp) = 1;

# with cache
SELECT COUNT(*) FROM database.partitioned_measures WHERE measure_timestamp >= '2016-01-01' AND DAYOFWEEK(measure_timestamp) = 1;


# MUST BE PRIMARY KEY
ALTER TABLE `bigtable` DROP PRIMARY KEY, ADD PRIMARY KEY (`id`,`trxdate`);



# get info 
# before partitioning
# -------------------
EXPLAIN SELECT * FROM bigtable; # detail
SELECT COUNT(*) FROM bigtable; # row info


# after partitioning
# ------------------
EXPLAIN PARTITIONS SELECT * FROM bigtable;
EXPLAIN PARTITIONS SELECT * FROM bigtable WHERE trxdate='2021-01-03';
EXPLAIN PARTITIONS SELECT * FROM my_requests WHERE t_id <> -1 AND rq_date = DATE_SUB(now(), INTERVAL 1 DAY)\G


# partition by range
ALTER TABLE bigtable PARTITION BY RANGE (TO_DAYS(trxdate))(
	PARTITION p0 VALUES LESS THAN (TO_DAYS('2021-02-03')),
	PARTITION p1 VALUES LESS THAN (TO_DAYS('2021-02-07')),
	PARTITION p2 VALUES LESS THAN (TO_DAYS('2021-02-11')),
	PARTITION p3 VALUES LESS THAN (TO_DAYS('2021-02-15')),
	PARTITION p4 VALUES LESS THAN (TO_DAYS('2021-02-19')),
	PARTITION p5 VALUES LESS THAN (TO_DAYS('2021-02-23')),
	PARTITION p6 VALUES LESS THAN (TO_DAYS('2021-02-27')),
	PARTITION p7 VALUES LESS THAN (MAXVALUE)	
);


# indexing table 
# source https://dev.mysql.com/doc/refman/8.0/en/create-index.html

CREATE INDEX part_of_name ON customer (name(10));
ALTER TABLE employees ADD INDEX idx (generated_col);

CREATE TABLE t1 (col1 INT, col2 INT, INDEX func_index ((ABS(col1))));
CREATE INDEX idx1 ON t1 ((col1 + col2));
CREATE INDEX idx2 ON t1 ((col1 + col2), (col1 - col2), col1);
ALTER TABLE t1 ADD INDEX ((col1 * 40) DESC);
CREATE INDEX jobTitle ON employees(jobTitle);


# some storage engines permit you to specify an index type when creating an index. For example:
CREATE INDEX id_index ON lookup (id) USING BTREE;

SHOW INDEXES FROM bigtable;


# partitioned indexes
# ===================
# dapat menggunakan partition index untuk mendukung partitioned tables atau untuk menyederhanakan index management. 
# index partition dapat bersifat lokal bagi partisi tabel atau dapat pula bersifat global yang berpengaruh pada seluruh tabel.

# DROP INDEX ADD INDEX

ALTER TABLE bigtable
    DROP PRIMARY KEY,
    DROP INDEX trxid,
    ADD PRIMARY KEY(trxid, meta_key, metaid), 
    ADD INDEX(metaid); 
    
# ============================================================================================================================
# Stick with only PARTITION BY RANGE(TO_DAYS(rq_date)).
# ============================================================================================================================

ALTER TABLE bigtable PARTITION BY RANGE(TO_DAYS(trxdate)) (
    PARTITION d0 VALUES LESS THAN (TO_DAYS('2021-03-03')),
    PARTITION d1 VALUES LESS THAN (TO_DAYS('2021-03-05')),
    PARTITION d2 VALUES LESS THAN (TO_DAYS('2021-03-10')),
    PARTITION d3 VALUES LESS THAN (TO_DAYS('2021-03-12')),
    PARTITION d4 VALUES LESS THAN (TO_DAYS('2021-03-15')),
    PARTITION d5 VALUES LESS THAN (TO_DAYS('2021-03-19')),
    PARTITION d6 VALUES LESS THAN (TO_DAYS('2021-03-22')),
    PARTITION d7 VALUES LESS THAN (TO_DAYS('2021-03-25')),
    PARTITION d8 VALUES LESS THAN (TO_DAYS('2021-03-27')),
    PARTITION d9 VALUES LESS THAN (TO_DAYS('2021-03-29')),
    PARTITION d10 VALUES LESS THAN (MAXVALUE)
);


#   * NOTE
#   - PARTITION BY HASH is useless.
#   - SUBPARTITIONing is useless.
#   - Hundreds of partitions is inefficient.


#   * Notif
# - Don't use PARTITIONing until you know how and why it will help.
# - Don't use PARTITION unless you will have > 1 Million rows
# - PARTITION BY RANGE is the only useful method.





# source
# https://stackoverflow.com/questions/44964442/how-does-indexes-work-with-mysql-partitioned-table
# https://www.vertabelo.com/blog/everything-you-need-to-know-about-mysql-partitions/#:~:text=Index%20Partitioning%3A%20Partitioning%20applies%20to,do%20not%20support%20foreign%20keys.