"If"??? I think they are still on the TBD list for MySQL, so at
the moment it is not an "if", it is an "as". <G>
Take a look at the column types PostgreSQL offers.
Among other interesting things, like a datetime type which works, it has
arrays.
You can define a column as an array of integers for instance.
Example :
Imagine we create a table to hold the index of a book. We should have
this (in pseudo-sql):
table words:
id integer primary key
word varchar
table refs:
page integer not null
word_id integer not null references words(id)
index on all interesting columns.
To find all pages where a word is, we look in "words" (index lookup on
word) and find the word id ; then we look in "refs" all pages with the
corresponding word_id
To find all words on a page, we look in "refs" (index lookup on page),
then we group by word, and we look in words to list the words.
With an array type, we have :
table words:
id integer primary key
word varchar
page integer[]
Then you can use a GIST index on page and ask it "find all rows where the
page list contains this page". This is a lot faster than the above Joins.
Getting the list of pages for a word is also a lot faster (it's only one
select, maximal locality of reference).
The GIST indexes are so optimized in Postgres that the speed for these
lookups is amazing.
I tested this feature by building a list of 100.000 words which appeared
in anything from 1 to 10 different random pages (between 1 and 1000). When
finding all words on a page, the GIST index had acceptable timings (ie. a
few tens of milliseconds to return between a few hundred and a few
thousands records) ; the pivot table had horrendous timings on the verge
of a second (because the Join was looking in the pivot table for the
condition first, then making index lookups for each row in this large
table, which is the only way to do it... and makes a lot of disk seeks !)
If you want to use a "minimal subset of SQL", you'll have to forgo these
interesting features...
Postgresql has indexable geometric types. On a random population of
500.000 points, it takes 90 ms to get the points in a certain bounding box
(this particular query returned 5.000 points)... without clustering on the
index it took about 200 ms...
So, if you want performance, it is difficult to ignore database-specific
points.
I know MySQL has geometric types too. But only for MyISAM tables !
(argh). So you don't get transactions on them. And MyISAM tables have
terrible write concurrency.
