On Fri, 29 Oct 1999, Mike Schwartz wrote:
> I think we agree on most things here.
> SELECT * FROM sitelog WHERE referrer LIKE '%yahoo%'
I expect that to work in MySQL in the future, but would now parse the
referer string before entering it, so that I can do this query without the
LIKE '%...' construct. I agree that you are safer with Oracle because it
is more mature, so things you realize, in retrospect, that you want to do
with your data may in some circumstances be significantly easier/faster
with Oracle. You pay a price for this, though.
> I don't know the history of Oracle development. I suspect this is a very cheap
> optimization, and I also suspect it would not be so hard to add to MySQL. I
> don't claim to be an expert on RDBMS internals, but I can see from the
> documentation that they do use indexes for LIKE 'foo%' clauses. That code must
> find the first 'foo%' and then walk forward through the index until it finds no
> match. Why would it take so much extra coding to go to the first key and walk
> forward through the index until the end of the index?
I expect it's not trivial. I would expect the key to be indexed as a
[balanced] tree. You enter at the root, and within a few comparisons,
you're at 'foo...'. Now all others are in the branches of your node. No
such thing for '%foo', for which you'd have to scan the entire index. For
LIKE '%foo' I'd create an index for the inverse ('oof...'), but I don't
know how one would optimize LIKE '%foo%'. Also, don't know the MySQL
internals. Maybe it is very easy to speed up the index scan, or maybe
MySQL incorrectly decides that the index is worthless (even though a
complete index scan is cheaper than a complete data scan).
Another aspect is that MySQL uses relatively small defaults for buffers.
Maybe it is as easy as letting it have as much memory as Oracle uses.
> I'm not sure that Oracle is as bad as you make it sound. When I do a du -s on
> the Oracle tree, I find it uses about 500M of disk. WOW! Now, I do a du -s on
> the bin directory and it's 454M. Then I do ls -l on the bin dir and see that
> there are 174 files and 454M. Then I look at the actual files and see that I
> have many .pre-glibcpatch files that I can certainly rm (though I choose not
> to) to save almost 1/2 the space. Then I also see that all the binaries are
> statically linked. I did not try to strip the binaries, either.
I agree that disk space is not that big a deal. Low mwmory use is nice on
systems with e.g. 32 MB RAM. And of course $s.
> This speaks directly to my comment that the EFFORT to make MySQL work well is
> doable and huge. It requires a specific knowledge of internal workings of
> MySQL algorithms and designing or restructuring yoru data to fit those
> algorithms. With Oracle, you don't have to go to this extreme.
What I meant was that developers of MySQL could be approached with
details about the problems. THEY might from that learn about uses not
anticipated and add optimizations to speed it up. I suspect that this has
happened many times with Oracle and that the current product reflects it.
> My point about DBM was simply that stating 'can handle large databases' doesn't
> mean so much. You can handle enormous large databases as a linear list, but
> the performance would be worse (and linearly worse) as the database grows.
> When MySQL developers talk about a 50M record database, I cannot imagine they
> are doing many LIKE '%foo' queries on it (or they'd make that query faster
> yesterday :) If they're doing single primary key lookups on individual
> records, it is certain to be plenty fast.
I'm sure this discussion get's them going, or actually that they'd been
thinking about this some time ago, but that it is harder and/or less
urgent than some other work, or that it'll be easier after other planned
> I agree. It doesn't make it less 'scary' to someone evaluating MySQL as a
> solution for the first time.
Good point. Maybe one should rephrase:
1. Feature present for a long time, no bugs discovered for the last x
months, and likely heavy use.
2. Same, but not used much.
3. Bugs found more recently significant/insignificant
. new relatively untested feature. - or something like that.
> To me, this is magic. It may require the most massive changes to the internal
> structure of MySQL, for all I know. It may also be a large part of the size of
> oracle :)
I thought that some ISAM index optimizing commands do just that (but less
> > I'm used to MySQL. Did some PostgreSQL. Terribly slow. A postgres developer
> > made it much faster in a few min (still slower than MySQL). Not fair to
> > ask an Oracle guy about the comparison. MySQL is a toy in comparison: much
> > less expensive, smaller, easier, nimbler.
> I don't think this is an insult to MySQL.
No, I don't think so either. MySQL is your pal, Oracle is the consultant
you pay to help you when you get stuck ;-)
> I would prefer to see them implement TABLESPACE instead. It would make it more
> compatible with other databases and solve the same problem.
I don't know TABLESPACE. I like the file-per-table setup. Terrible pain
when something goes wrong with e.g. a MS Access database file ...
Fred Lindberg, Inf. Dis., WashU, St. Louis, MO, USA