How to Control Query Cache Behavior in MariaDB

Galaxy Glossary

How do I control query cache behavior in MariaDB?

MariaDB lets you enable, disable, or clear the query cache using SQL_CACHE / SQL_NO_CACHE hints, cache variables, and FLUSH commands.

Sign up for the latest in SQL knowledge from the Galaxy Team!
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.

Description

What does the MariaDB query cache do?

The query cache stores complete result sets for identical SELECT statements. When the same query arrives again, MariaDB can skip execution and return the cached rows instantly, lowering latency and CPU usage for read-heavy workloads.

When should I use SQL_CACHE or SQL_NO_CACHE?

Use SQL_CACHE when a query runs often with identical text and the underlying tables change infrequently.Use SQL_NO_CACHE for volatile tables or ad-hoc queries to avoid useless cache fills and invalidations.

How do I turn the cache on or off globally?

Set query_cache_type to ON (every eligible SELECT is cached), DEMAND (only SQL_CACHE is cached), or OFF. Adjust query_cache_size to allocate memory. Changes need SUPER privileges.

How do I clear or defragment the cache?

FLUSH QUERY CACHE defragments memory while keeping cached results.RESET QUERY CACHE removes all entries and frees memory immediately—handy after bulk loads into Products or OrderItems.

What parameters matter most?

query_cache_size defines the memory pool; values under 2 MB disable the cache. query_cache_limit caps the size of a single cached result. query_cache_min_res_unit controls internal block size and fragmentation.

Does the cache help write-heavy ecommerce apps?

Probably not. Each INSERT, UPDATE, or DELETE on Orders or OrderItems invalidates all cached queries that reference those tables.Heavy DML traffic can erase most performance gains.

Best practices for production

Start with query_cache_type = DEMAND, cache only known hot SELECTs, monitor Qcache_hits vs Qcache_inserts, and keep query_cache_size modest (≤256 MB) to reduce mutex contention.

Example: speeding up dashboard totals

An analytics dashboard repeatedly fetches yesterday’s revenue. Mark the query with SQL_CACHE, schedule a nightly RESET QUERY CACHE after ETL loads, and enjoy millisecond responses during the day.

.

Why How to Control Query Cache Behavior in MariaDB is important

How to Control Query Cache Behavior in MariaDB Example Usage


-- Cache yesterday’s revenue for quick dashboards
SELECT SQL_CACHE SUM(total_amount) AS yesterday_revenue
FROM Orders
WHERE order_date = CURRENT_DATE - INTERVAL 1 DAY;

How to Control Query Cache Behavior in MariaDB Syntax


-- Cache a frequent read
SELECT SQL_CACHE total_amount
FROM Orders
WHERE order_date = CURRENT_DATE - INTERVAL 1 DAY;

-- Bypass cache for real-time inventory checks
SELECT SQL_NO_CACHE stock
FROM Products
WHERE id = 42;

-- Turn cache on only when SQL_CACHE is specified
SET GLOBAL query_cache_type = DEMAND;

-- Allocate 128 MB to the cache
SET GLOBAL query_cache_size = 134217728; -- 128 * 1024 * 1024

-- Defragment cache memory without dropping results
FLUSH QUERY CACHE;

-- Purge all cached results (use after bulk loads)
RESET QUERY CACHE;

Common Mistakes

Frequently Asked Questions (FAQs)

Is the query cache safe for replication?

Yes, cached results never replicate. Each replica maintains its own cache, so enabling it does not affect binary logging.

Can I cache prepared statements?

Yes, but the SQL text must be byte-for-byte identical, including whitespace and comments, for the cache to recognize a hit.

Should I use the cache on Galera clusters?

Generally no. High write rates and cluster invalidations tend to nullify benefits. Benchmark carefully before enabling.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Company
BlogFeaturesPricingAboutCareersFeature Requests
Resources
Resource LibraryDocsSupportBrand Kit
Explore
Product TourData Job BoardRecruitersConsultantsStartup ToolsMemes
Learn
Learn SQLBeginner ResourcesData ToolsSQL Interview PrepCommon ErrorsSQL OptimizationSQL In Other LanguagesGlossary
Comparisons
DatagripTablePlusDBeaverBeekeeper StudiopgAdminMySQL WorkbenchSQL Server Management StudioPosticoDbVisualizerNao LabsCursor
HexBrieferPopSQLSherloqBlazeSQLSweep AIWaii AIOuterbase
© 2025 Galaxy Software Inc.
hello@getgalaxy.io
Terms of ServicePrivacy PolicySecurity
Reddit IconProduct Hunt IconTwitter / X IconLinkedIn Icon