N8CIA Database Migration: From MySQL to SQLite

For years, the N8CIA ham radio database ran on MySQL. It worked, but it was heavy. Imports of FCC and DMR data would spike CPU and memory usage so badly that the entire web server slowed to a crawl. I had to run updates overnight just to keep the site online.

Eventually, I decided to explore alternatives. But first, I gave MySQL every chance to improve.

Tuning Up MySQL with AI Help

Before making a change, I ran a full round of performance tuning with AI assistance. Using AI coding tools, I restructured imports, optimized indexing, and adjusted buffer and cache settings.

Those changes helped. Imports ran faster, and memory stabilized. But MySQL still pushed the system to its limits. Large updates maxed out CPU and memory, driving load averages into double digits. MySQL’s architecture is designed for multi-user environments, not the read-heavy nature of a ham radio lookup service.

That is when I started seriously looking at SQLite.

Rethinking SQLite

I used to think SQLite was only for mobile apps or small utilities. I never imagined it could handle a full-scale website with millions of records.

That assumption was completely wrong. SQLite handled large tables with ease and used only a fraction of the resources. For databases that mostly serve read requests with minimal writes, it turned out to be a perfect fit.

Why SQLite Made Sense

SQLite is lightweight, fast, and easy to maintain.

• No external database server
• Minimal resource use
• Excellent read performance
• Simple backup and recovery

It is like swapping a 1 kW amp for a 5 W QRP rig that gets the same message through quietly and efficiently.

The Switch

I built a flexible database layer that could use either MySQL or SQLite, making it easy to transition without rewriting the entire site. Once SQLite was live, I redesigned the import process to take advantage of its strengths:

1. Write-Ahead Logging (WAL) Mode
2. Allows reads during writes, keeping the site fully responsive.
3. Retry Logic for Index Creation
4. Handles temporary locks automatically.
5. Graceful Error Handling
6. Keeps the process running even if a table is busy.
7. Automatic Indexing
8. Callsign, name, and DMR fields are optimized for fast lookups.

The Results

Performance Comparison

MySQL (Before):

• Import Time: 15–25 minutes

• Load Average: Double-digit spikes

• Memory Use: Maxed out

• System Stability: Unreliable during imports

SQLite (After):

• Import Time: 2 minutes, 26 seconds

• Load Average: 3.77 (stable)

• Memory Use: Minimal

• System Stability: Fully stable

Even during large imports, the websites now stay completely responsive.

Database Sizes

• FCC Database: ~1.0 GB

• DMR Database: 46 MB

Each database is a single file, simple to back up or move.

Query Performance

Searches for callsigns and names now return in milliseconds, no matter the size. Case-insensitive lookups run cleanly with proper indexing, and performance is equal to or better than MySQL.

Efficiency and Simplicity

SQLite requires almost no maintenance.

• No separate service to manage
• No tuning or configuration overhead
• Easy file-based backups
• Reads and writes can occur simultaneously under WAL mode

The system now runs quietly and efficiently.

Final Thoughts

The AI tuning helped me confirm that MySQL was performing as well as it could. The real breakthrough came from simplifying. SQLite delivered the same functionality with far less complexity and overhead.

Full rebuilds now complete in a few minutes, queries are instant, and the site remains stable no matter what. For ham operators maintaining call lookup or repeater databases, SQLite is more than capable. It is dependable, efficient, and low-maintenance.

— 73, Denny, N8CIA