Need for Speed: Optimizing MySQL 3.23 MyISAM Tables

In late 2000, MySQL 3.23 is the standard for anyone building a real web application. While some are still talking about Oracle or SQL Server, we know that for high-read web traffic, nothing beats the speed of MyISAM. Forget InnoDB and its overhead-if you need lightning-fast SELECT queries, MyISAM is the choice.

But out of the box, MySQL 3.23 is tuned for a machine with 64MB of RAM. If you're running a professional server with 512MB or 1GB, you're leaving performance on the table.

The key_buffer_size: Your Most Important Setting

MyISAM caches indexes in memory using the key_buffer_size. If your indexes don't fit in RAM, MySQL has to hit the disk for every query. This is a performance killer.

# Edit your my.cnf (usually in /etc/my.cnf)
[mysqld]
# Give MySQL about 25-30% of your total RAM for index caching
key_buffer_size = 128M
# Increase the sort buffer for faster ORDER BY operations
sort_buffer_size = 2M
# Table cache should be high enough to avoid reopening .MYI files
table_cache = 512

Table Structure and Fixed-Length Rows

For maximum performance, your MyISAM tables should be Static (Fixed-Length). This means avoiding VARCHAR, TEXT, or BLOB fields if possible. If every row is the same length, MySQL can calculate the exact position of a row on disk with simple math, rather than scanning.

-- Fast: Static Table
CREATE TABLE products (
    id INT(11) NOT NULL AUTO_INCREMENT,
    sku CHAR(16) NOT NULL, -- Use CHAR instead of VARCHAR
    price DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (id),
    KEY (sku)
) TYPE=MyISAM;

If you absolutely must use VARCHAR, run OPTIMIZE TABLE regularly to defragment the .MYD file.

EXPLAIN: Stop Guessing

If a query is slow, don't just add indexes blindly. Use the EXPLAIN command. You want to see "Using index" and as few rows scanned as possible.

EXPLAIN SELECT * FROM products WHERE sku = 'PROD-100';

If the type is ALL, it’s a full table scan. Add a KEY immediately.

The Query Cache

MySQL 3.23.41 introduced the Query Cache. This is a game-changer. It stores the actual result sets of your SELECT queries. If the underlying data hasn't changed, MySQL returns the result instantly without even parsing the SQL.

# Enable the query cache
query_cache_type = 1
query_cache_size = 32M

Avoid 'transactions' (BDB or early InnoDB) if your workload is 90% reads. The locking overhead will slow you down. MyISAM's table-level locking is rarely a bottleneck for web apps if your queries are fast. Keep your writes batchable and your reads indexed.

---

Aunimeda builds backend systems with optimized database architectures - PostgreSQL, Redis, ClickHouse, and more.

Contact us for backend and database engineering. See also: Custom Software Development