Re: algorithms for selecting on non-key/non-indexed field

> >> In practice, mysql is probably doing something clever that I'm
> >> not. I would like any comments on the clever bits in
> >> particular. Looking at the source for both mysql and msql, it
> >> appears that they create in-memory indexes behind the scenes
 to
> >> help you out, or possibly gather statistical information on
> >> field contents to get an approximation when doing the select.
> >
> >Congrats on being able to understand the code of both mysql and
 msql. You
> >must've noticed that msql abuses memory usage quite a bit,
 making it
> >invariably quite fast but only until you start running out of
 memory. If
> >you want to use Berkeley DB, I'd advise against trying to
 simulate
> >relational databases (if mysql and msql qualify), but rather to
> >concentrate on the task at hand and using the proper access
 methods for
> >the proper job.
>
> Interesting commentary. I would lean toward a similar argument,
> however to avoid starting a flame war with someone who is really
> religious about mysql/msql, I'll just focus on your comment
> about BDB not being proper. I was looking at the source for
> mysql, and found that it uses a set of file primitives that
> closely match BDB. I also took a look at my old school text
> (Fundamentals of Database Systems, Elmarsi/Navathe) and they
> mention a similar set of file primitives as a basis.
>
> My first question: Why would BDB not be appropriate? Is this
> an architectural issue - say sophisticated buffering or the API
> is too generic ? It would seem that for a relational database,
> one would need a set of primitives for files consisting of Fixed-
> Length Records, Variable-length Records, and B-Tree's in order
> to provide a solution to your queries.
>
Where/How did I say BDB wasn't appropriate? Putting words in my mouth will get you nowhere fast. Now lets see if I can get anywhere by putting words in your mouth. What you really want to know is how to create more than one indexed field using BSD, right?

If so, lets take the example of a table which contains the fields "key", "date" and "data" where the two first fields must be indexed. Considering the key is a string and date an integer, lets use a hash table and btree index respectively (so that you can search for date ranges, for example). You can then create the following structure for your database: method | key | data
-------+------+------

recno  | #    | string data
hash   | key  | struct { date; #; }
btree  | date | struct { key; #; }

Using this structure, you can access all fields by either looking for the key or the date. You now have the start of what looks like a multiple field database.

If this is not what you're looking for, I have just proven how putting words in other people's mouths leads nowhere. Received on Sun Jul 02 2000 - 00:00:00 CEST