List:General Discussion« Previous MessageNext Message »
From:Frederik Lindberg Date:January 1 1970 12:00am
Subject:Re: mysql is a pain in the ass
View as plain text  
On Fri, 29 Oct 1999, Mike Schwartz wrote:

> I think we agree on most things here.

Yes.
> 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.

Agreed.
 
> 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
code restructurings.
 
> 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
visibly).
 
> > 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 ...

-Sincerely, Fred

Fred Lindberg, Inf. Dis., WashU, St. Louis, MO, USA

Thread
mysql is a pain in the assMark O. Davis26 Oct
  • Re: mysql is a pain in the assEd Carp26 Oct
  • Re: mysql is a pain in the assLindsay Davies26 Oct
  • Re: mysql is a pain in the assfooler26 Oct
  • Re: mysql is a pain in the assBogdan Paduraru26 Oct
  • RE: mysql is a pain in the assDavid Moreno S.26 Oct
  • Re: mysql is a pain in the assJeremy Cole26 Oct
  • Re: mysql is a pain in the assMark Russell26 Oct
  • Re: mysql is a pain in the assMark Russell26 Oct
  • Re: mysql is a pain in the assRuben I Safir26 Oct
  • Re: mysql is a pain in the assRuben I Safir26 Oct
  • Re: mysql is a pain in the assTonu Samuel26 Oct
  • Re: mysql is a pain in the ass(Paul D. Smith)26 Oct
  • Re: mysql is a pain in the assfooler27 Oct
  • Re: mysql is a pain in the assfooler27 Oct
    • Re: mysql is a pain in the assEd Carp27 Oct
  • Re: mysql is a pain in the assfooler27 Oct
  • Re: mysql is a pain in the assfooler27 Oct
    • Re: mysql is a pain in the assEd Carp27 Oct
      • Re: mysql is a pain in the assfooler27 Oct
        • RE: mysql is a pain in the assOzlinx)27 Oct
        • Re: mysql is a pain in the assEd Carp27 Oct
          • Re: mysql is a pain in the assRuben Safir27 Oct
      • Re: mysql is a pain in the assJay J27 Oct
      • Re: mysql is a pain in the assTonu Samuel27 Oct
        • Re: mysql is a pain in the assDan Nelson27 Oct
        • Re: mysql is a pain in the assDan Nelson27 Oct
      • Re: mysql is a pain in the assfooler27 Oct
        • Re: mysql is a pain in the assJani Tolonen27 Oct
          • Priorities on the TODO list [was Re: mysql is a pain in the ass]Bob Kline27 Oct
            • MySQL and ZendStephen J. Taranto27 Oct
              • Re: MySQL and Zend(Sean McKenna)28 Oct
        • Full commercial products vs. open source [was Re: mysql is a painin the ass]Bob Kline27 Oct
        • RE: mysql is a pain in the assOzlinx)28 Oct
      • Re: mysql is a pain in the assJay J27 Oct
      • Re: mysql is a pain in the assTonu Samuel27 Oct
      • Re: mysql is a pain in the assTonu Samuel27 Oct
      • Re: mysql is a pain in the assfooler27 Oct
        • Re: mysql is a pain in the assBob Kline27 Oct
        • Re: mysql is a pain in the assRuben Safir27 Oct
        • Re: mysql is a pain in the assMike Schwartz28 Oct
          • Re: mysql is a pain in the assJani Tolonen28 Oct
            • Re: mysql is a pain in the assMike Schwartz29 Oct
              • Re: mysql is a pain in the assFrederik Lindberg29 Oct
                • Re: mysql is a pain in the assMike Schwartz29 Oct
                  • Re: mysql is a pain in the assFrederik Lindberg29 Oct
                    • Re: mysql is a pain in the assMichael Widenius14 Nov
                      • LIKE '%foo%' (was: mysql is a ...)Frederik Lindberg14 Nov
                  • Re: mysql is a pain in the assMichael Widenius14 Nov
                    • Re: mysql is a pain in the assBob Kline14 Nov
      • Re: mysql is a pain in the assTonu Samuel28 Oct
      • Re: mysql is a pain in the assfooler29 Oct
        • Hardware vs Software and the speedJani Tolonen29 Oct
      • Re: Hardware vs Software and the speedfooler29 Oct
        • Re: Hardware vs Software and the speedJani Tolonen29 Oct
      • Re: mysql is a pain in the asssasha29 Oct
      • Re: Hardware vs Software and the speedfooler30 Oct
        • Re: Hardware vs Software and the speedJani Tolonen1 Nov
      • Re: mysql is a pain in the assDuncan Kinder30 Oct
  • Re: mysql is a pain in the assSabat27 Oct
  • Re: mysql is a pain in the ass(Paul D. Smith)27 Oct
Re: mysql is a pain in the assOzlinx)26 Oct
RE: mysql is a pain in the assJohn Foley26 Oct
  • RE: mysql is a pain in the assBob Kline26 Oct
  • mysql GUI ClientBronco26 Oct
    • Re: mysql GUI Clientjurban26 Oct
Re: Hardware vs Software and the speedfooler30 Oct
  • Re: Hardware vs Software and the speedsinisa30 Oct
Re: mysql is a pain in the assJoseph R. Junkin30 Oct
  • Re: mysql is a pain in the assLarry Rufo30 Oct
    • Re: mysql is a pain in the assBlain Nelson1 Nov
  • Re: mysql is a pain in the assMike Schwartz1 Nov
Re: Hardware vs Software and the speedEd Carp1 Nov
Re: mysql is a pain in the assEd Carp1 Nov
  • Re: mysql is a pain in the assLarry Rufo2 Nov
Re: mysql is a pain in the assDejan Lekic1 Nov
Re: mysql is a pain in the assRaul Miller1 Nov
  • Re: mysql is a pain in the asssinisa2 Nov
    • Re: mysql is a pain in the assMatthias Urlichs2 Nov
    • Re: mysql is a pain in the assRaul Miller2 Nov
      • Re: mysql is a pain in the assPatrick Greenwell2 Nov
        • Re: mysql is a pain in the assRaul Miller2 Nov
          • Re: mysql is a pain in the asssinisa3 Nov
            • Re: mysql is a pain in the assRaul Miller3 Nov
              • Re: mysql is a pain in the asssinisa4 Nov
                • Re: mysql is a pain in the assRaul Miller4 Nov
              • Re: mysql is a pain in the assMichael Widenius14 Nov
                • Re: mysql is a pain in the assRaul Miller14 Nov
      • Re: mysql is a pain in the asssinisa3 Nov
        • Re: mysql is a pain in the assRaul Miller3 Nov
          • Re: mysql is a pain in the assMichael Widenius14 Nov
  • Re: mysql is a pain in the assDave Hodgkinson2 Nov
    • Re: mysql is a pain in the assRaul Miller2 Nov
Re: mysql is a pain in the assEd Carp3 Nov
  • Re: mysql is a pain in the assRaul Miller3 Nov
    • Re: mysql is a pain in the assMike Wexler3 Nov
      • Re: mysql is a pain in the assMatthias Urlichs7 Nov
        • Re: mysql is a pain in the assRaul Miller8 Nov
        • Re: mysql is a pain in the assMichael Widenius28 Nov
      • Re: mysql is a pain in the assMichael Widenius28 Nov
  • MySQL 3.23.7? (was)Re: mysql is a pain in the assVan28 Nov
    • Re: MySQL 3.23.7? (was)Re: mysql is a pain in the asssinisa28 Nov
  • Re: MySQL 3.23.7? (was)Re: mysql is a pain in the assTonu Samuel28 Nov