7.1.3. What We Have Used MySQL For
This section describes an early application for MySQL.
During MySQL initial development, the features of MySQL were
made to fit our largest customer, which handled data warehousing
for a couple of the largest retailers in Sweden.
From all stores, we got weekly summaries of all bonus card
transactions, and were expected to provide useful information
for the store owners to help them find how their advertising
campaigns were affecting their own customers.
The volume of data was quite huge (about seven million summary
transactions per month), and we had data for 4–10 years
that we needed to present to the users. We got weekly requests
from our customers, who wanted instant access to new reports
from this data.
We solved this problem by storing all information per month in
compressed “transaction tables.” We had a set of
simple macros that generated summary tables grouped by different
criteria (product group, customer id, store, and so on) from the
tables in which the transactions were stored. The reports were
Web pages that were dynamically generated by a small Perl
script. This script parsed a Web page, executed the SQL
statements in it, and inserted the results. We would have used
PHP or mod_perl instead, but they were not
available at the time.
For graphical data, we wrote a simple tool in C that could
process SQL query results and produce GIF images based on those
results. This tool also was dynamically executed from the Perl
script that parses the Web pages.
In most cases, a new report could be created simply by copying
an existing script and modifying the SQL query that it used. In
some cases, we needed to add more columns to an existing summary
table or generate a new one. This also was quite simple because
we kept all transaction-storage tables on disk. (This amounted
to about 50GB of transaction tables and 200GB of other customer
data.)
We also let our customers access the summary tables directly
with ODBC so that the advanced users could experiment with the
data themselves.
This system worked well and we had no problems handling the data
with quite modest Sun Ultra SPARCstation hardware (2x200MHz).
Eventually the system was migrated to Linux.
