MySQL: The Tables Didn't Have to Become One File

A bookstore has a catalog and a record of sales. The catalog knows the books’ prices. The sales record knows how many copies sold. Somebody wants a total.

One solution is to copy the catalog details into every sales record. Another is to keep the two collections separate and describe how their rows relate. The second approach introduces a join: match the relevant records when the answer is needed.

MySQL’s early work put a SQL interface over existing table machinery, giving applications a practical way to request those combinations. The interesting part is the relationship between asking for an answer and finding a route to obtain it.

The total can look reassuringly simple. The assumptions beneath it deserve more attention.

Beginnings weren’t a public download

MySQL’s company retrospective identifies Allan Larsson, David Axmark and Michael “Monty” Widenius as founders, with a first version in May 1995. It places the first external release in August 1996, as a Solaris binary. Public 3.11.1 followed in October, Linux binaries in November and source in December.¹

The working year here is consequently 1995 for the internal beginning. It doesn’t mean a reader could have downloaded the publicly distributed server that May.

The project’s own history describes an attempt to connect its fast low-level ISAM routines through mSQL. Finding that interface inadequate for its speed and flexibility needs, the developers made a new SQL interface with a deliberately similar API to ease application porting.²

That is a concrete development motive. Useful storage code already existed; the question was how applications should access it. Compatibility with an existing programming interface could reduce the work required to move those applications, even while the database implementation changed.

A new database doesn’t arrive with every capability it eventually acquires. MyISAM belongs to the July 1999 3.23 alpha, and the GPL transition to June 2000. Neither is an original 1995 feature.¹

For a period technical reference, this article uses the preserved MySQL 3.22.14b-gamma manual. Its basic join and aggregate syntax supports the worked query below. That later 1990s manual isn’t evidence that every detail shown was already implemented in the private first version.³

Two tables and one relationship

Here is an invented bookstore dataset. Prices are integer cents; we avoid introducing floating-point rounding into an example about row matching.

The books table contains:

id	title	price_cents
10	Maps After Dark	1250
20	Small Engines	800
30	The Patient Compiler	1900

The sales table contains:

sale_id	book_id	quantity
101	10	2
102	20	1

The identifiers are chosen teaching values, not real catalog entries. books.id is intended to identify one catalog record. sales.book_id refers to that identifier. A sale’s own identifier answers a different question: which sale record is this?

A query can match the records and calculate their contributions:

SELECT SUM(books.price_cents * sales.quantity) AS total_cents
FROM books, sales
WHERE books.id = sales.book_id;

The comma-separated table list and equality condition use the period manual’s documented join form. SUM adds the resulting expression values.³

For the first sale, identifier 10 matches Maps After Dark. Two copies at 1,250 cents contribute 2,500 cents. The second sale matches identifier 20 and contributes 800 cents.

The total is:

$$2(1250) + 1(800) = 3300 \text{ cents}.$$

The Patient Compiler doesn’t contribute because there is no matching sale in our data. Its existence in the catalog isn’t itself evidence of revenue.

Original explanatory diagram, 2026. Invented catalog and sales records; selected matched values produce 3,300 cents. Not a database screenshot or historical benchmark.

The output combines information while leaving the stored collections conceptually separate. Producing a report doesn’t require turning the catalog and sales into one permanent file with every detail repeated.

The condition carries the meaning

Remove the equality condition from the query, and every book can combine with every sale. Our three books and two sales then produce six pairs.

That is a valid combination with the wrong meaning for this report. The server doesn’t know that the author forgot a relationship. It follows the request it received.

In this case, the prices sum to 3,950 cents and the quantities sum to three. Combining every price with every quantity produces 11,850 cents. The query still returns a neatly formatted number.

That is a particularly unpleasant kind of error: the arithmetic is correct for the wrong rows.

The equality condition narrows the combinations to the relationships we intended. It doesn’t merely make the query faster. It defines which contributions belong in the answer.

Our saved checker executes the displayed query against the invented data in modern SQLite and verifies 3,300. It also verifies the unqualified combination’s six rows and 11,850 total. SQLite is a convenient independent check of the example’s relational arithmetic, not proof of running the original MySQL server.

The historical manual supplies evidence for the period query syntax. The modern execution supplies a reproducible check of what this small dataset means.

Missing records can disappear from a total

Now add a sale with book_id = 99, quantity one. There is no catalog record with identifier 99.

Our original matching query contributes nothing for that sale. The total remains 3,300 cents. The new sale hasn’t become a free book; it has failed to find the record needed for the calculation.

This is a limitation of interpreting the output without inspecting the data relationships. A matching query can answer “total the matched sales” while the reader assumes it means “total every sale.” Those statements agree only when the relevant catalog records exist.

A separate diagnostic can look for the missing relationship. The period manual documents a left join that retains an unmatched row and uses a null test to find it:³

SELECT sales.sale_id, sales.book_id
FROM sales LEFT JOIN books ON sales.book_id = books.id
WHERE books.id IS NULL;

For our extra sale, this produces its identifier and 99. We assume the catalog’s id values are non-null, so a null on the joined side distinguishes an absent match.

The diagnostic makes incompleteness visible. It doesn’t supply the missing price or decide how to repair the catalog. Those are application and data-management decisions.

The preserved 3.22 manual also warns that its FOREIGN KEY, CHECK and REFERENCES clauses didn’t enforce the relationships implied by their names.³ A declaration that parses isn’t necessarily a protection that operates. Assuming modern enforcement would conceal precisely the failure our example needs to notice.

A duplicate can multiply the answer

Consider the opposite problem in an intentionally damaged teaching dataset. Suppose a second catalog row also uses identifier 10 and the same 1,250-cent price.

The sale for two copies now finds two matching rows. Each pair contributes 2,500 cents. Add the 800-cent sale, and the result becomes 5,800 cents.

The query hasn’t accidentally charged each copy twice through multiplication. The relationship has supplied twice as many matches as the report expected.

A correctly enforced unique catalog identifier would prevent this particular duplicate. Our damaged-table demonstration deliberately removes that assumption so the failure is visible. It doesn’t claim that inserting duplicates into a declared primary key should succeed.

This illustrates why a join is more specific than “look up the price.” A relationship can produce zero matches, one match or several. The answer depends on which cardinality the data allows and which cardinality the author intended.

Missing and duplicated records pull in different directions. One hides a contribution; the other repeats it. Both can leave the final total plausible enough to survive a casual glance.

An index changes the route

The query describes the matching rule. It needn’t specify the exact physical search strategy.

In a simple teaching approach, take each sales row and scan the catalog for matching identifiers. With 1,000 catalog entries and 100 sales, that can require up to 100,000 identifier comparisons if every catalog row is examined for every sale.

An indexed lookup offers a different route to the relevant catalog entries. It can avoid scanning the entire catalog for each identifier. That is a conceptual explanation of why an index helps, rather than a measurement of a particular MySQL query plan.

Original explanatory diagram, 2026. A conceptual index-assisted route; no claim that this is the exact plan chosen by an early MySQL release.

The index doesn’t permanently glue the catalog to the sales. It organizes access to stored keys. The report still depends on the requested relationship and the current data.

Nor does an index repair identifier 99. A fast search can establish very quickly that the required row isn’t there. Efficiency makes a correct question cheaper; it doesn’t turn an incomplete dataset into a complete one.

This division of labor is appealing. An application describes which records belong together, while the database can choose how to find them. The choice has costs: indexes need storage and maintenance when data changes. The best route depends on table sizes, available indexes and the query’s conditions.

A small demonstration can explain the opportunity without inventing a speedup multiplier. It can’t tell an administrator how their real installation performs under load.

A report needs a time model too

There is one final assumption in the bookstore example. It multiplies quantities by the price currently in the catalog.

Change Maps After Dark from 1,250 to 1,400 cents, and the same two sale records now produce 3,600 cents. The older sale quantity hasn’t changed; the catalog price has.

That may be appropriate for estimating replacement value. It is a poor way to reconstruct historical revenue if the customer paid the earlier price. A real sales record may need the actual sale-time price as its own fact.

The issue isn’t that separate tables are wrong. It’s that “price” has more than one possible meaning. Avoiding redundant descriptions and preserving historical transaction facts are different requirements.

Our checker verifies the price-change result too. The exercise keeps asking what the number represents, rather than stopping when SQL produces an answer.

MySQL’s early interface made this kind of relational work practical for applications. Its significance lies partly in providing access to combinations that a program could describe without manually arranging every file traversal.

The server can match the rows. The report’s author still has to decide which matches mean money.

Footnotes

1. Scott Stroz, MySQL Developer Advocate, MySQL Retrospective — The Early Years, December 1, 2024. Internal May 1995 versus external August 1996, subsequent distribution milestones, MyISAM 1999 and GPL 2000 distinguished. ↩ ↩²

2. MySQL’s creator-team history account, preserved in the modern manual. Used only for ISAM/mSQL interface origins and API compatibility motive, not modern features. ↩

3. Original MySQL 3.22.14b-gamma language reference, especially aggregates, SELECT/JOIN and CREATE TABLE caveats. Later 1990s technical evidence, not private 1995 syntax verification. Original data and modern SQLite checks don’t establish historical runtime execution. ↩ ↩² ↩³ ↩⁴