MySQL is too slow
MySQL is too slow? Evaluate MemCP Database
If slow SQL queries are holding back an application, evaluate MemCP Database before accepting ever larger MySQL servers, more caches, or a separate OLAP copy as the only options. MemCP is an open-source, MySQL-protocol-compatible, compressed columnar database built for querying operational data in memory.
MemCP aims to be a fast, compact alternative for MySQL-style applications whose bottleneck is scanning, joining, grouping, sorting or aggregating operational data. It combines compressed column storage, late materialization, adaptive indexes, parallel shards and a Scheme-based query compiler.
That performance ambition is part of the product, even while MemCP remains Beta. It does not mean every current query beats mature MySQL: point workloads, connector behavior and application suites such as WordPress can still lose when a plan stays interpreted, misses a JIT fast path, compiles too much work or exercises an unsupported optimization. Those results identify roadmap work rather than changing MemCP's design goal.
Observed performance profile
Project measurements have shown two deliberately different sides of the current system:
| Workload | Observed result | Interpretation |
|---|---|---|
| OLAP, aggregation, and search-oriented workflows | Speedups of 10× and more over MariaDB/PostgreSQL in measured cases | RecSets, compressed column scans, batching, late materialization, and parallel execution match the workload |
| Conventional OLTP application paths | Currently about 1.3–2.0× the elapsed time in measured cases | Broader JIT coverage, compile cost, point paths, and connector compatibility remain optimization work |
| Complete WordPress- and wiki-style page builds | No significant difference in overall page-loading time in the measured application workflows | End-to-end impact depends on application work and on the number and mix of SQL queries |
| Filtered list over roughly one million documents | Around 30 seconds on PostgreSQL versus 1.6 seconds on MemCP for the same query | The search/list path is the urgent migration candidate even if the smaller OLTP path is not yet faster |
Write-heavy tables using ENGINE=logged |
About 10× the write throughput of safe in project measurements |
Avoids commit-time fsync, but recent committed writes can be lost on kernel crash or power failure
|
The practical conclusion is not that every statement must move at once. Move the workflows where physical design dominates: filtered lists, search, aggregation, dashboards, and mixed operational analytics. Keep or shadow OLTP paths until their compatibility and latency are acceptable, then expand the migration as JIT and point-query execution improve.
These figures describe observed application and benchmark cases, not portable guarantees. The detailed record should name versions, queries, data distribution, hardware, durability, concurrency, cache state, successful response counts, and raw samples as required by Performance Measurement.
Durability is part of the comparison. logged retains a WAL and survives a MemCP process crash, but it does not promise power-loss durability. For replaceable, flash-backed data, sloppy avoids a continuous WAL and normally publishes compressed storage every 15 minutes, reducing SD-card write pressure at the cost of losing the unrebuilt delta after an unclean stop. See Persistency and Performance Guarantees.
When MemCP can help
- dashboards repeatedly aggregate fresh transactional data;
- wide tables are queried through a small subset of columns;
- repeated categories, ranges or sequences compress well;
- analytical scans should run beside writes without maintaining a separate OLAP copy;
- SQL over HTTP or an embedded endpoint can remove an application/database network hop;
- reconstructible caches and durable business tables need different persistence trade-offs.
Common SQL performance problems
These are the symptoms users usually see before they search for another database. Each one should be tested as a complete query and application workflow, not reduced to an isolated operator.
MySQL query slow on a large table
A query that was fast with thousands of rows may cross a threshold at millions of rows: a scan touches too many row pages, an index returns too many candidates, or fetching wide rows dominates after lookup. Adding another index can help one predicate but cannot turn a wide row store into a compact column scan.
MemCP reads only referenced columns, keeps suitable values compressed and selects a scan/index/RecSet path from cardinality and reuse costs. This is especially relevant when a page needs a few output columns from a wide document or event table.
PostgreSQL WHERE plus ORDER BY LIMIT is slow
The difficult shape is not a plain LIMIT. It combines selective or correlated filters with an order that favors another access path:
SELECT id, title, changed_at
FROM documents
WHERE tenant_id = ?
AND state IN ('open', 'review')
AND EXISTS (SELECT 1 FROM permissions p
WHERE p.document_id = documents.id
AND p.user_id = ?)
ORDER BY changed_at DESC
LIMIT 100;
A conventional optimizer may walk the ordering index and reject rows until it finds 100 matches, or materialize/filter a large intermediate result before sorting. MemCP can represent the permission/filter domain as a RecSet, intersect it with other boundaries, read ordered candidates in batches and stop when enough accepted rows have been emitted. See RecSets, Scan, and Advanced SQL Tutorial.
GROUP BY, COUNT DISTINCT or dashboard query is slow
Repeated dashboards often aggregate a small number of columns across a large, recently updated relation. MemCP can scan compressed values in batches, compute shard-local partial aggregates, and cost reusable group caches or computed columns. The application continues to query current operational data instead of waiting for an ETL copy merely to answer a dashboard.
Search and filtered lists become slower as documents grow
Faceted lists combine permissions, tags, states, text/prefix filters, joins, counts, sorting and pagination. Their cost can grow with the candidate domain even though the user sees only 20 or 100 rows. This is the workflow in which project measurements have produced speedups of 10× and more over MariaDB/PostgreSQL. In one concrete list over roughly one million documents, the same query took around 30 seconds on PostgreSQL and 1.6 seconds on MemCP.
RecSets are designed to carry compact membership between those operators without copying wide result rows. Ordered batch acceptance and late materialization then avoid doing full output work for every candidate.
MySQL INSERT or UPDATE becomes slow because of fsync
If durable commit latency is the bottleneck, first batch statements within the application's atomicity budget. Where process-crash recovery is sufficient and power-loss risk is externally accepted, ENGINE=logged removes commit-time WAL synchronization and has reached about 10× safe write throughput in project measurements.
This is a durability choice, not a harmless tuning flag. Keep irreplaceable data on safe; see Persistency and Performance Guarantees.
Database writes are wearing out a Raspberry Pi SD card
For reconstructible telemetry, caches, imports or derived state, ENGINE=sloppy avoids continuous WAL traffic and normally publishes a compressed generation during the 15-minute rebuild cycle. This batches flash writes and makes the possible loss of the unrebuilt delta explicit. Durable business records still belong on safe and appropriate storage media.
Why it can be fast
MemCP reduces bytes moved through the cache hierarchy, reads columns in batches, delays row materialization, executes independent shards in parallel and can fuse filter, projection and aggregation. The planner decorrelates supported subqueries before choosing direct scans, adaptive indexes, RecSets, group caches, computed columns or ordered top-k paths. Supported Scheme hot paths can be compiled by the x86-64 JIT.
This architecture attacks common SQL-performance costs directly:
| SQL performance problem | MemCP approach |
|---|---|
| Large scans move whole rows | Read only referenced compressed columns in batches |
| Repeated GROUP BY or correlated lookups | Cost reusable group caches, computed columns, indexes, or RecSets |
| ORDER BY builds a large temporary result before LIMIT | Use ordered scans, late materialization, top-k selection, and braking where semantics permit |
| Application-to-database round trips dominate | Use the MySQL protocol, SQL over HTTP, or a narrowly scoped in-database endpoint |
| One row-store must serve transactions and analytics | Keep recent writes in delta storage while scanning compressed main columns |
Try the slow query on MemCP
Start an isolated instance with persistent storage:
docker volume create memcp_eval_data docker run -d --name memcp-eval \ -e ROOT_PASSWORD='choose-a-password' \ -v memcp_eval_data:/data \ -p 127.0.0.1:4321:4321 -p 127.0.0.1:3307:3307 \ carli2/memcp:latest
Then import a copy of the relevant schema and data, run the real query through the same client path, and inspect its physical plan:
EXPLAIN PHYSICAL SELECT ...;
Do not stop at a synthetic SELECT 1. Test the query that users actually wait for, with representative cardinalities, concurrency, authentication, durability and cold/warm states. Migration from MySQL and PostgreSQL describes a staged evaluation; Advanced SQL Tutorial explains the optimizations visible in the plan.
Evaluate honestly
Import a copy, validate results, restart and recovery, then benchmark the real authenticated workload against the same data and durability requirement. Record plans and compilation time as well as execution time. A current loss caused by missing JIT coverage, planner choice or compatibility work should be published with that diagnosis, not hidden and not generalized into a claim that MemCP cannot become faster for the workload.
The outcome should be actionable: if MemCP returns the same results, satisfies the required durability and operations gates, and improves the real workload, migrate that workload instead of continuing to tune around the limitations of an unsuitable physical design. Keep MySQL available as the source of truth or rollback path until those gates have been demonstrated.
Frequently asked questions
Is MemCP a drop-in replacement for MySQL?
MemCP speaks the MySQL client protocol and implements a broad, tested SQL subset, but it is not a byte-for-byte implementation of every MySQL feature. Verify all application-critical queries, metadata calls, transactions, constraints and connector behavior. See Supported SQL and Database Tools compatibility with MemCP.
Is MemCP an MCP server for AI assistants?
No. MemCP Database is a relational SQL database. It is unrelated to Model Context Protocol memory servers that happen to use a similar name.
No. memcpy() is a C/C++ library function for copying memory. MemCP Database is a server and storage engine queried with SQL, HTTP, RDF or embedded Scheme interfaces.
Start with Comparison: MemCP vs. MySQL, Migration from MySQL and PostgreSQL, Advanced SQL Tutorial, Performance Measurement and Current Status and Open Issues.