The Gmail email client

January 7, 2001
By Robert Bernstein

SQL Made Easy

A sample vfolder properties dialogue.

One does not need to be a SQL guru to set up virtual folders in Gmail, since a central tenet guiding its development is to make SQL queries available without requiring any knowledge of SQL syntax on the part of the user. While a full-fledged query-builder is still only in the planning stage, the process has been simplified a good deal. There is no need to write complete SQL SELECT statements, and the process is documented so that most users, perhaps with some trial and error, can easily produce working virtual folders. For instance, here are the definitions of two of this writer's Gmail folders:

For the Snort IDS (http://www.snort.org) email list:

(subject LIKE  "%Snort%" AND direction = "Incoming")

For the Gmail discussion email list:

((subject LIKE  "%Gmail%" OR tofield LIKE "%gmail-discuss%")
    AND direction = "Incoming")

Here, from the Gmail User's Guide, is a description of the matching fields available for these folder definitions:

"Gmail uses two tables to store emails. Why two? Because one is fixed length for fast access, and the other contains data which is variable in length.

"The fixed length table is called 'display', the vfolder usable fields are: date, subject, fromfield, readstatus (Read, Unread, Sent or Queued), direction (Incoming or Outgoing).

"The other table is called 'details', the vfolder usable fields are: headers (all the headers from the email), message (the body of the email), tofield, ccfield, bccfield, attachments (not in use yet)."

For each message in the database, Gmail "caches" its "folder matches," i.e. a list of all the queries that message matches, for speedy navigation among different vfolders. Whenever a vfolder query is added, deleted or modified, this matching index must be rebuilt, but Gmail automatically prompts the user whenever this step is necessary. This is the one Gmail operation that can be time-consuming, depending on the speed of the machine and the size of the message base. On the author's test machine, an old K6-233, a database of 4450 messages with ten vfolders required two minutes fifty four seconds for this step.

Although Gmail's performance does not slow down as the size of the message base increases (with the one exception just noted), eventually it may be desirable to archive the messages one has accumulated. The mbox export is perfect in this context; after accumulating, say, six months of the dozen or so Debian (substitute your favorite distro) lists one subscribes to, those six months worth of messages can be read out into one mbox file for long term storage. There's no need to keep track of twelve archives, one for each list.