tuning-mysql-in-query-sql-cache-optimization.sql

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


SHOW VARIABLES LIKE 'have_query_cache';

SHOW VARIABLES LIKE 'query_cache_%';


# alter any of these variables using the SET GLOBAL or SET SESSION statements
SET GLOBAL query_cache_size = 16777216; 

# starting the MySQL profiler, which is an analysis service for monitoring the performance of MySQL queries.
SET profiling = 1;

# query without using the query cache
SELECT SQL_NO_CACHE * FROM bigtable; 

# query with the cache
SELECT SQL_CACHE * FROM bigtable;  

# compare this data in the next steps when query cache is enabled, so keep note of duration. you can ignore the warning within the output since this simply indicates that SHOW PROFILES command will be removed in a future MySQL release and replaced with Performance Schema.
SELECT * FROM bigtable;
SHOW PROFILES;


# example (subquery) subquery is certainly legal
SELECT r.trxid, f.prodid FROM warehouse AS r, product AS f WHERE r.trxid = f.trxid AND r.prstatus = 1 AND f.idrac IN (SELECT idrac FROM onlinetrans WHERE ractype = 727);

# equivalent join would run faster due to MySQL’s optimization algorithms
SELECT r.trxid, f.prodid FROM warehouse AS r, product AS f, onlinetrans AS a WHERE r.trxid = f.trxid AND f.idrac = a.idrac AND r.prstatus = 1 AND a.ractype = 727;