Become a fan of Slashdot on Facebook

 


Forgot your password?
Close
typodupeerror
×
Programming IT Technology

MySQL 4.0 Released 207

Posted by Hemos from the lock-yer-rows-gentlemen dept.
egerlach writes "All you DB admins out there might be interested to know that MySQL 4.0 has finally been released! It's only 4.0.0 alpha, but you can download it here. You can also check out a full list of changes."
This discussion has been archived. No new comments can be posted.

MySQL 4.0 Released More

MySQL 4.0 Released

Comments Filter:

  • unicode support? (Score:1, Interesting)

    by Anonymous Coward
    from this ./ article [slashdot.org] In 4.0, to be released in mid-October: 'support for the Unicode character set, the SSL (Secure Sockets Layer) protocol, embedded database links and multitable updates' but still no where to be seen?
  • Does this new release of MySQL support the proper use of foreign keys internally? (found no info in release notes). Or has MySQL a different way of implementing a "One-Many"-relation?

    My university (NTNU.no) is using an old version of MySQL (from 1998), which does nothing when you set a foreign key.

    • According to the article [slashdot.org] linked by this slashdot story, foreign key support should show up in 4.1 along with subselects and a few other goodies. Would be nice to have these BASIC features...

    • Yes (Score:3, Informative)

      by Betcour ( 50623 )
      Yes it does, if you install and use the latest InnoDB table handler (see www.innodb.com )

    • Does this new release of MySQL support the proper use of foreign keys internally? (found no info in release notes). Or has MySQL a different way of implementing a "One-Many"-relation?




      You don't absolutely need foreign keys to implement one-to-many relationsships - you can just do it the standard way and drop the foreign key constraint. Of course, your app needs to make sure that it updates and deletes entries when necesarry (and when did NTH^WNTNU stop using Oracle? That's what we used when I had the database course there, and MySQL is a poor choice for a course in databases If there's one place you'd like to really have foreign keys, subselects, transactions, views etc, a university course on databases is it - and PostgreSQL clearly has better SQL support than MySQL)

      • You don't absolutely need foreign keys to implement one-to-many relationsships - you can just do it the standard way and drop the foreign key constraint.

        Right, but it is really handy to be able to enforce referential integrity when a record gets deleted. That way you don't have dangling foreign keys...

        I still use MySQL for most basic fast databases (particulalrly those which do not have many deletions) and PostgreSQL for the more sturdy, rugged, business-critical ones.
  • Hm... curious how many new keywords they introduced into the new version. Probably I've used all of them as column names anyways... grrr....

  • But why? (Score:3, Informative)

    by chris.bitmead ( 24598 ) on Tuesday October 16, 2001 @06:46AM (#2435439)
    "The new version is intended as a platform for building mission critical, heavy load database solutions"

    Still no proper transactions, no subselects, no foreign keys or views. How can this be a "mission critical" SQL database? I still don't get why people use it. Sure, for some situations you can get it to work, but why bother [postgresql.org]?

    • Transactions, foreign keys (Score:5, Informative)

      by blackcat++ ( 168398 ) on Tuesday October 16, 2001 @06:59AM (#2435470)
      I hate to disappoint you but transactions, foreign keys and row-level locking are available [innodb.com] and seem to work quite well.
      • Still no sub-selects, which IMHO is the most basic of the many things MySQL lacks.
        • Nested queries and stored procedures are due in a 4.1 release scheduled for this December, according to this [zdnet.com] article.
        • subselects are pretty basic these days yes, but hardly a showstopper for "mission critical".
      • Anyone know if there are plans to pull the InnoDB code directly into MySQL, since both are GPL and since InnoDB is now part of the MySQL source distribution [mysql.com]? That would clear up the tech-marketing confusion over transactions etc., and would have justified the 4.0 version++.

        I'd hazard a guess that there'd also be opportunities for performance optimisation if they concentrated on just one table handler, though they might also thereby lose some flexibility along the way.

      • (This is a legit observation, not a troll). I love it how they advertise that "Slashdot", the site I visit way too often and is DOWN way too often(!), uses innoDB. I'm not saying it's a MySQL problem, but how many posts have we seen that says, "Sorry, the DB server crashed... again". Not my idea of a promo!
        • I would say that is more due to Slashdots codebase than MySQL. I have done load balancing tests with MySQL with software I've written that on lesser hardware than Slashdot uses handling a higher load. I haven't looked at the slashdot code recently, but there were a dozen and a half different methods behind the database functions that could have been optimized to better utilize MySQL.


          Any database, if not used very thoughtfully, will have serious problems. I had a problem with a Sybase database due to a silly bug (wasn't sharing the dbh handle like it should have) that was crashing Sybase on an enterprise Sun server. So, my point is, I would really look at other sources to find out the quality and stability than Slashdot. I'm not bashing the Slashdot code base, I think it performs it's function better than any M$ piece of software out, but there are a lot more stable applications reliant on MySQL DB's to benchmark against.

          • "I haven't looked at the slashdot code recently, but there were a dozen and a half different methods behind the database functions that could have been optimized to better utilize MySQL."

            We welcome specific suggestions and criticisms. Please submit a Slash bug report [sourceforge.net] and let us know which methods you're talking about. Thanks!

            • I wasn't saying anything in specific. I'd have to look at the current codebase as mentioned. In response to the parent of my original post, I haven't seen a "Database not available" style error message for quite some time, I was trying to illustrate that at the time not only was MySQL not very mature, but the slashdot code base had a lot of work to do.


              Right now, my only gripe with Bender is that it drops the login. This now seems to be quite a bit better as of late, hasn't happened for a few weeks but I'm assuming this has already been reported.

              • I'm not sure what you mean about Bender "dropping the login"... we did rework the cookie system a fair bit for Fry (Slash 2.1/2.2) so I'm guessing that bug is long-fixed.

                Most of the errors that bring Slashdot down are because we're really pushing the boundary of MySQL and have been for a while. In the last few weeks the cutting edge has caught up with us so it should be more stable now. Which means of course it's time to go to 4.0 because it wouldn't be Slashdot unless something breaks twice a day.

                • lol. What I mean with the login thing is sometimes I'll refresh the page and my login information disappears. When I try to login, it refuses for a while. I come back 30 minutes later to try, and it works fine. Last time this happened was about 2 weeks ago. If this isn't fixed I'll start documenting when/if it happens again.
                • I think the behavior he is referring to (dropping the login and then after a few refreshes he gets back in) is what happenes when /. is down on the backend (assuming here). In other words when teh site goes into static mode with all .shtml files. This happens quite often. You can try to login but get the static pages anyway. - Thats my take on it anyway since the sites will go static for a while then ban g- I'm back in afer a refresh and everythign is runnign dynamically like it should - I always assumed this was DB problems on teh backend.
        • To date we had one bug in Innodb that I have found (and we found one other in replication, but no one would have noticed that other then the fellow who has to keep this stuff running...). We have had some growing pains that we have went to. In our first week of operation we had a number of hardware problems bring us down (we have since moved hardware) which was only related to the DB's because MySQL was running on that Hardware. This http://www.tangent.org/~brian/talks/dbsummit_scali ng/ [tangent.org] take you to some slides that I did for a talk I gave on Slashdot's DB.
    • >Still no proper transactions
      have you had a look at the innodb-backend in mysql?

      >no subselects
      will be in 4.x afaik

      >no foreign keys
      partially implemented for the innodb-backend.

      Please have a look at the product before posting FUD.

      • Subselects being in a future release means that asserting that they are not in the current release is not FUD. It's a freakin' fact.

        Also, if innodb has all the missing functionality, why not just have that be the default table type and be done with this crap?

    • Re:But why? (Score:2, Informative)

      by macsforever2001 ( 32278 )

      One database is not better than another because it has a bigger checklist of features. If that were true, then we'd all be using Oracle (which is actually a very good RDBMS). MySQL has advantages and disadvantages over PostgreSQL. The same is true for almost any database.

      Let's look at your complaints of MySQL lacking features one by one:

      no proper transactions

      Yes it does! [mysql.com] If you use certain table types.

      no subselects

      This is a nice feature, but *not* necessary. Many times a proper JOIN can be used instead. Alternately you just use multiple SQLs. However, this is the one missing feature of MySQL that I want the most.

      no foreign keys

      You don't need foreign keys to maintain referential integrity. A proper GUI, among many other things, can enforce this anyway. It is a nice feature, but definitely not needed in a well designed system. Further they slow down performance and I have seen projects where they are not used because of this.

      views

      These can be nice too, but I personally never use them. They are simply not required in any project I've ever seen. Actually I think views are confusing because they mask the real tables. I think this is a style issue more than anything else, YMMV.

      How can this be a "mission critical" SQL database?

      How about better performance [mysql.com].

      Don't get me wrong. I like PostgreSQL too. They are both great and both are very useful in production environments. Why does there only have to be one open source database? The competition is truly healthy for both products and ultimately for us developers.

      • Re:But why? (Score:3, Informative)

        by chris.bitmead ( 24598 )
        What a troll. They do a performance test of
        postgresql without running the vacuum analyzer
        to update statistics. Then they "wonder" (as
        if they didn't know) why postgresql is slow.
        Duh!

      • No, no, no (Score:4, Interesting)

        by NineNine ( 235196 ) on Tuesday October 16, 2001 @07:43AM (#2435569)
        You don't need foreign keys to maintain referential integrity. A proper GUI, among many other things, can enforce this anyway. It is a nice feature, but definitely not needed in a well designed system. Further they slow down performance and I have seen projects where they are not used because of this.



        This is a terrible suggestion. Unless you're developing a tiny application that only you will be using, anybody who doesn't use foreign keys is completely incompetent. I know, it sounds harsh, but it's true. Not using foreign keys is like writing an application with just one long main() procedure. A. You're assuming that only one gui and no users will ever access this database. B. If the GUI isn't perfect, your data is garbage. C. It makes future analysis and upgrades very difficult, if not impossible.

        These can be nice too, but I personally never use them. They are simply not required in any project I've ever seen. Actually I think views are confusing because they mask the real tables. I think this is a style issue more than anything else, YMMV.

        Wow. Views are useful for security, and for performance.

        No offense guy, but you know absolutely nothing about databases. You really should not be giving out advice on them. Pick up a BASIC database theory book and read it. When you're done, read it again. You have a lot to learn.
        • Most developers use foreign key constraints during development, to test if the code they've written is correct. In production environments, often the foreign key constraints are removed. It's an old discussion if this is a good practice or not. Personally I use stored procedures to provide an API to the data, and my experience is that foreign key constraints are a pain in the ass, because testing is a hell, you can't just remove records since the constraints will stop you. Others disagree on this. IMHO it's however not correct to call it a 'terrible suggestion', to rely the integrity of the data on the interface of the data, since the data in the tables will be inserted through that interface. (and if your set of constraints aren't perfect, your data is garbage ;)). For testing purposes, you need foreign key constraints though.
        • >> This is a terrible suggestion. Unless you're developing a tiny application that only you will be using, anybody who doesn't use foreign keys is completely incompetent

          I used to work for a company in Spain that developed a pair of applications for an important bank here (supposed to be #1 according to some studies). They had the policy of NOT TO USE FK NEVER (of course, we are not talking about mainframes, but some Oracle databases). My personal impression is that they did that because it simplified development.

          How can you call incompetent to someone that just doesn't use FK? (disclaimer: I try use them everytime I can) "You've got a lot to learn"? Let me see. So, you have read one book and think that everyone that doesn't agree to that concrete book doesn't have a clue?

          Sorry, but I don't agree either. I prefer to use FK for my own reasons. But I also have programs working without primary keys. And the world didn't fall out for this.

      • Re:But why? (Score:3, Informative)

        by Wdomburg ( 141264 )
        Let me preface my reply by saying I do in fact admin a fairly large MySQL installation, and it performs better than one would expect with its limitations. However, that is no reason to gloss over its deficiencies.

        >>no proper transactions
        >Yes it does! If you use certain table types

        All of which are fairly new and account for only a small portion of the installations, meaning that they are no where near as well tested as the default table type (MyISAM).

        Not to mention that none of them seem to have reliable benchmarks available. And to make matters worse, InnoDB has a big banner on their front page comparing themselves to a "leading database" but if you click on the link and read through the text, they state:

        "Note that the tests were not run in exactly
        the same way for the other database: the
        comparison does not satisfy strict standards."

        Publicizing the results of an admittedly flawed benchmark is unprofessional and, in my opinion, highly unethical.

        >>no subselects
        >This is a nice feature, but *not* necessary.
        >Many times a proper JOIN can be used instead.
        >Alternately you just use multiple SQLs.
        >However, this is the one missing feature of
        >MySQL that I want the most.

        Agreed - it is possible to work around this issue. Though it does increase client code complexity.

        >>no foreign keys
        >You don't need foreign keys to maintain
        >referential integrity. A proper GUI, among many
        >other things, can enforce this anyway. It is a
        >nice feature, but definitely not needed in a
        >well designed system. Further they slow down
        >performance and I have seen projects where they
        >are not used because of this.

        Yes it is possible to do integrity checks programmatically. However, this does nothing for manual administration, and requires implementation for every piece of code that might modify the database.

        In most intstances I would consider not using foreign keys to be a poor decision, particularly after dealing with the mess created by a database where they decided to use programmatic checks for integrity.

        As a side note, there is partial implementation of foreign keys in the InnoDB table handler, though it has some fundamental flaws to it. It drops constraints on an ALTER table, it allows you to drop referenced tables, and it lacks features such as CASCADE ON DELETE.

        >>views
        >These can be nice too, but I personally never
        >use them. They are simply not required in any
        >project I've ever seen. Actually I think views
        >are confusing because they mask the real tables.
        >I think this is a style issue more than anything
        >else, YMMV.

        Views make it possible for you to modify the schema of a database without having to touch your client code.
        Views are also a wonderful way to present a simplified view to your programmers, rather than expecting them to know, e.g. how to do a full outer join on three or four tables with a sub-select thrown in just to make it a little more confusing. :)

        >>How can this be a "mission critical" SQL
        >>database?
        >How about better performance [mysql.com].

        As I have pointed out before, the benchmarks on mysql.com are for a single thread of access only. Which does not mimic the real world environment of the vast majority of database installations. Unless that is going to be your method, those benchmarks are essentially useless.

        To their credit, representatives from MySQL AB have promised a more robust test in the future. But until that's out, I cannot put any stock in their published benchmarks.

      • Note: I use PostgreSQL. I have little faith in MySQL, although I once used it for a project many moons ago. Hopefully it has improved since then.

        no proper transactions

        Yes it does! [mysql.com] If you use certain table types.

        I'd appreciate it more if it was the default table type and I didn't have to worry about it.

        no subselects

        This is a nice feature, but *not* necessary. Many times a proper JOIN can be used instead. Alternately you just use multiple SQLs. However, this is the one missing feature of MySQL that I want the most.

        Agreed.

        no foreign keys

        You don't need foreign keys to maintain referential integrity. A proper GUI, among many other things, can enforce this anyway. It is a nice feature, but definitely not needed in a well designed system. Further they slow down performance and I have seen projects where they are not used because of this.

        Which would I rather do? A ton of extra code to enforce something the database should be doing in the first place? Personally, I don't want to waste my time coding something that should be a basic feature of any database system. I'm not going to re-invent the wheel here. A well designed system should include a proper database management system.

        views

        These can be nice too, but I personally never use them. They are simply not required in any project I've ever seen. Actually I think views are confusing because they mask the real tables. I think this is a style issue more than anything else, YMMV.

        I use them now and again, and they're pretty useful. I can get away with not using them if there aren't too many users on a system, but what if I have multiple admins or database users that need to see some data on a table but not necessarily all of the data? This is more of a backend thing and could be worked around in the application itself, of course, but if I have multiple developers working on a project and I'd rather they see only what they need to see, views are extremely useful.

        How can this be a "mission critical" SQL database?

        How about better performance [mysql.com].

        You're actually quoting MySQL's own benchmarks? Of course they're going to make it look like MySQL rules the planet. Do you also trust all of Microsoft's benchmarks? Or Oracle's? Unless the benchmarks are done by a third party, I wouldn't put too much faith in them.

        J

    • Re:But why? (Score:2, Informative)

      by cpfeifer ( 20941 )
      My previous project (a commercial software product) evaluated the mjaor free DBs (postgreSQL, mySQL...) and we had to select mySQL because it was the only free DBMS with a large user base (for support), active development (again, for support), and a Windows port.

      Our DBA wanted to use postgreSQL for some of the reasons mentioned in these fine posts. However, we had a requirement to be able to run in a pure Windows or Solaris 8.x environment. MySQL is the only major, free DBMS that fits that bill.

      I know there's a million bitty-little open source/free ware dbms projets out there, but we didn't have time to fix any bugs/issues that we might've found. We had enough code of our own to write, we didn't have time to fix anyone else's product. I know this goes against the whole open source dogma, but sometimes it's the truth in industry.

      • Re:But why? (Score:4, Informative)

        by killmenow ( 184444 ) on Tuesday October 16, 2001 @08:19AM (#2435684)

        we had a requirement to be able to run in a pure Windows or Solaris 8.x environment. MySQL is the only major, free DBMS that fits that bill.
        Umm, how about Interbase [ibphoenix.com] (or Firebird [sourceforge.net] for that matter)? Definitely qualifies as "major" IMO, is being actively developed, and runs on Windows, Linux, Solaris, and others...

        We had enough code of our own to write, we didn't have time to fix anyone else's product
        So basically, you were just looking to get a DB you didn't have to pay for, didn't have to support, and didn't have to contribute anything back to. In other words: trying to get something for nothing...you're right, that is often the "truth in the industry."

    • Re:But why? (Score:1, Troll)

      by msheppard ( 150231 )
      I don't think any of the features you list are a must have for "mission critical." Maybe if you only now how to implment database functionality with those functions... but that's like saying a programming language is worthless becuase it doesn't have a tertiary operator. Maybe a developer who only knows how to program with that operator will become useless on the new language, but someone who LEARNS TO ADAPT will thrive.

      Transactions: I seem to find transaction support [mysql.com]

      SubSelects: You don't need to do these, it can be worked around pretty easily.

      Foreign Keys: Again, many simple workarounds.

      Views: Good ridance. A maintenance nightmare.

      If everyone just keeps doing everything the same, we're gonna end up with a lot of budweiser.

      M@

      • Re:But why? (Score:5, Informative)

        by micromoog ( 206608 ) on Tuesday October 16, 2001 @08:27AM (#2435710)
        SubSelects: You don't need to do these, it can be worked around pretty easily.

        Views: Good ridance. A maintenance nightmare.

        You clearly have no knowledge whatsoever of databases. Maybe you don't use subselects in CS102: Intro to Databases, but there are many things that can ONLY be accomplished with subselects. The simplest subselects would be worked around using temporary tables (which unnecessarily complicates the application), and the more complex requirements cannot be worked around at all (go look up "nested subquery").

        Views are one of the greatest things ever to happen to a real-world database system. Various applications like their data organized in different ways, and the creation of views allows a single dataset to be used for all. The workaround is to create and populate separate tables (now that's a "maintenance nightmare").

        The bottom line: a "mission-critical" database needs to support these things, and the others listed above. People would much rather use a different system that supports these features than rewrite their existing applications around the limitations of MySQL.

        • I wouldn't say that there are some things that *ONLY* subselects can achieve. Maybe in a single query, but you *CAN* code around it. You basically just use the main selection, and your 'subselects' just get put in a loop. Inefficient, perhaps, but it works until we get real subselects.

        • Stupidity runs rampant in our industry (Score:4, Interesting)

          by Morocco Mole ( 121389 ) on Tuesday October 16, 2001 @12:43PM (#2436929)

          Well said! I agree with you!


          On my last contract I was unable to convince project leads of the value of transactions. Even though my resume clearly shows 10 years of Oracle and 6 years of SQL Server I couldn't convince a bunch of idiots {with an admitted combined total of SQL Server experience of less than 4 weeks} that being able to transactionally update a patient record and the related information about which medications had been administered was a good idea. Their stated reason for not wanting views, transactions, foreign keys, and stored procedures? "Our database is small - only a thousand or so patients per hospital. Transactions would reduce performance. We don't want to use stored procedures because some day we might want to port the database. What's a view? What's a foreign key?"



          So after a few weeks of gently, but fruitlessly, trying to explain that stored procedures and views will guarantee the performance you want, that foreign key constraints and transactions will guarantee the integrity that your medical device database must have - I finally couldn't take it anymore.



          So one day in a meeting I said: "Can a patient be hurt if a medication is administered twice? What if the power goes down while updating a patient's treatment record and information about a treatment is lost?"



          "Yes a patient could be harmed by duplicate treatments, but that won't happen..."



          So I said: "I cannot help you..." And I walked off the gig. I dunno what came of that project but I did hear from a friend that 6 months later they had a GUI that featured several screens that took between 30 seconds to a full minute to bring up one screenful of information. People just don't get the golden rule: Code defensively and keep your business logic as CLOSE to the disk as you can! There are alot of astoundingly ignorant people out there and you just can't stop them all...



          --Richard


          • And I walked off the gig.

            That took a lot of integrity. It's really nice to hear about this. Thanks.
          • So I said: "I cannot help you..." And I walked off the gig.

            You did the right thing. Too bad that level of integrity isn't universal; your replacement was probably foolish enough to go along with it.

            I have to wonder what the FDA, HHS, HCFA and other Federal agencies would think about such reckless disregard for patient safety. I suspect various regulators might take an issue with it. Even if they didn't, it's just begging for a multimillion-dollar "wrongful death" civil suit, sooner or later.

            With the health-care industry's penchant for "risk management", you'd think they would jump at the opportunity to avoid potential future lawsuits by designing in more data integrity from the start, expecially when advised to do so by the experts they hire. Go figure...
        • You clearly have no knowledge whatsoever of databases.
          Ouch! Sad, but true. I'm trying to learn more though, thanks for the info.

          A point I feel I failed to make was that databases shouldn't all be excatly the same and support all the exact same ways of doing things. I believe many DBA's are "feature dependant" and that's the reason they resist a new DB with out these features they depend on.

          I think you hit the nail on the head with the "knowledge of databases", but you describe a "knowledge of the features of database X."

          Maybe another worthless analogy: Many developers know COM but do not understand OO. So when they try to do OO in another language, they can't, becuase they didn't understand the IDEA, they only knew the IMPLMENTATION.

          M@

    • Still no proper transactions, no subselects, no foreign keys or views. How can this be a "mission critical" SQL database?

      Clearly, it's optimized for applications that perform

      select *
      from table1

      over and over and over again.

  • In other news (Score:5, Funny)

    by brunes69 ( 86786 ) <slashdot@keir[ ]ad.org ['ste' in gap]> on Tuesday October 16, 2001 @06:48AM (#2435444)

    In other news, PostgreSQL [postgresql.org] announced that it has all the features that MySQL 4.0 has, has had them for a long time, is GPL'd, and is very stable (as in not alpha). Get it here [postgresql.org]

    OK, maybe that wasn't announced, but it should have been.

    • Re:In other news (Score:4, Informative)

      by chris.bitmead ( 24598 ) on Tuesday October 16, 2001 @06:51AM (#2435452)
      Actually, it's not GPLed, its BSDed. i.e.
      nearly, almost public domain.
    • MySQL is GPL too and has been for a long time now. In fact, a lot of the complaints about MySQL is caused by ignorance (I'm not saying all of them, stay calm). So please, check the facts before flaming MySQL.

      --
      Rasher

      • Re:In other news (Score:3, Interesting)

        by brunes69 ( 86786 )
        I never said it wasn't GPL. I'm just tyring to point out that a much better and more robust open source database already exists, and frankly I do't understand why people would continue to use a product so limited as MySQL (it doesnt even support sub-selects!).

        • Re:In other news (Score:2, Insightful)

          by shayne321 ( 106803 )
          and frankly I do't understand why people would continue to use a product so limited as MySQL

          I'm not arguing FOR MySQL here, but I just wanted to point out I think the main reason people use it is accessibility. I started out programming CGI apps with perl using MySQL databases about 5 years ago with no prior database or programming experience. Over those years I've written tons of code and designed hundreds of databases. All of my current knowledge has come from books, MySQL documentation, and real world experience. Coming from this background, I started with MySQL because it was highly recommended as an easy to use database, and at the time was considered to be much faster that Postgres.

          MySQL is very easy for the newbie to get up and running. You don't have to understand views, foreign keys, transactions, etc. All you have to understand is "put data in with insert, pull data out with select"... Well, maybe more than that but you get the idea.

          That said, I've recently switched one of my major production databases over to postgres. I finally "graduated" to the point where I needed views, row-level locking, and some other things that I couldn't get with MySQL. I'm finally comfortable with postgres but it took some time. Postgres requires a bit more of it's users. For example, queries are case sensitive. If you have a web form that says "enter name of user to lookup" and feed that form to a query, "where user = 'bob'", you have to have extra code to convert 'bob' to 'Bob', 'BOB', or whatever. Either that or train your users better (yeah, right). You could argue that this is the "correct" behavior and I'd agree with you, but still, it's a PITA to rework already working programs to account for this.

          All of that said, I still use MySQL for some things.. I have several databases that get updated once daily via a cron job from a production database and are searched hundreds of times per minute by a mod_perl-enabled web site. Why bother with the complexities of having to explicitly set up keys and indexes, worry about case-sensitive matching, vacuum analyzing, etc, for such a simple database? In my mind this only requires more work and introduces more chances for errors.

          It all boils down to the right tool for the job. Writing a complete accounting package? Use postgres (or something commercial). Writing a simple interface to query a list of students in a classrom? Use MySQL.

          Shayne

    • Re:In other news (Score:3, Insightful)

      by Kattare ( 528707 )
      Sometimes speed is everything...

      • Re:In other news (Score:5, Insightful)

        by tzanger ( 1575 ) on Tuesday October 16, 2001 @07:23AM (#2435523) Homepage

        Sometimes speed is everything...

        Which MySQL hasn't got, no matter how you look at it.

        Postgres has shown time and time again that it blows the shit out of MySQL for any kind of select statement where your 'where' clause is even just slightly more complicated than 'x = y'. Even for simple selects, Postgres performance scales waaaaay better than MySQL. We're not talking hundreds of clients, either; we're talking dozens. MySQL falls flat on its face under heavy load.

        And if your application just requires the simplest of simple selects, a hashed flat file is faster than MySQL because you don't actually have to parse up the SQL statement and return the result in a formatted fashion!

        Face it: MySQL is a neat toy but that's about it. All this pandering about what 4.0's got now and how they think it's mission-critical-ready is bullshit, plain and simple. We've got an open-source, free ACID-compliant database already. MySQL has lost, they just don't know it yet.

        • Re:In other news (Score:4, Informative)

          by dhogaza ( 64507 ) on Tuesday October 16, 2001 @08:10AM (#2435652) Homepage
          Actually the InnoBase table type removes the biggest reason why MySQL traditionally has shit its pants under heavy load, as it provides row-level locking and non-blocking writes rather than the old table-level locking required with MySQL's original table type.

          So it should be much better in this regard.

          I don't know from personal experience, though. I use PostgreSQL instead because it's got important features like referential integrity checking and even better, a development team who understand why such features are important.
        • "MySQL falls flat on its face under heavy load.

          *cough cough* slashdot

          Perhaps one should know what one is talking about before siding so strongly in a religious debate.

          • Re:In other news (Score:4, Interesting)

            by SuiteSisterMary ( 123932 ) <slebrun.gmail@com> on Tuesday October 16, 2001 @09:23AM (#2435929) Journal
            You ever read slashdot lately, you go to the home page, and you're not logged in? So you read a story, and you try to change the threshold and sort order, and it either goes to main page, or to a 'recent topics' page, or back to the default view of the story? That's mySQL having fallen over. Slashdot, who's admins "reboot the MySQL server" *shudder* to fix things.

          • Re:In other news (Score:3, Interesting)

            by tzanger ( 1575 )

            *cough cough* slashdot

            You're giving /. as an example of a rock-solid stable system? I surely hope you're kidding. /. Has fallen up and down more times than I care to remember.

            IIRC, /. heavily caches both stories and the front page to avoid load on the MySQL server. Before this was done the crashes were a lot more visible. Now, you just don't see new comments until the cache is refreshed. This is both a good and a bad thing, but it does not show that MySQL works well under load.

            /. Also shows its MySQL troubles when you try to log in or change your viewing prefs; if the SQL server is down, you get the threaded (ick) cached page instead of what you want.

            here [phpbuilder.com] is a phpbuilder test that helps back up my claims.
        • Postgres has shown time and time again that it blows the shit out of MySQL for any kind of select statement where your 'where' clause is even just slightly more complicated than 'x = y'.


          I'm using MySQL to perform selects that join five tables, including one with over 800,000 rows and one with over 5 million rows. It can perform these selects in a fraction of a second, more than adequate performance for my application. MySQL has also been very reliable for me.


          I find the people here slamming MySQL are often doing so based on their theoretical, rather than practical knowledge. But perhaps you are different. I'd like to see you back up your claim.

          • How about a competition? (was Re:Proof, please) (Score:4, Insightful)

            by King Babar ( 19862 ) on Tuesday October 16, 2001 @09:48AM (#2436044) Homepage
            Postgres has shown time and time again that it blows the shit out of MySQL for any kind of select statement where your 'where' clause is even just slightly more complicated than 'x = y'.

            I'm using MySQL to perform selects that join five tables, including one with over 800,000 rows and one with over 5 million rows. It can perform these selects in a fraction of a second, more than adequate performance for my application.

            I might be missing something, but I don't see anything contradictory in these two statements. If your "where" clause is sufficiently easy and you can take advantage of indexing, you would expect any decent system to perform reasonably well when it is returning a handful of rows. Five table joins would only be scary when you can't toss out the vast majority of your 5 million rows at step one.

            MySQL has also been very reliable for me.

            One thing that has just occurred to me in this, round 5,408 of the MySQL-versus-PostgreSQL flame fest is to ask if anybody has tried to replace MySQL with PostgreSQL or vice versa in any project that both sides would consider "worthy" in some sense. One problem here, of course, is that if you really wanted an ACID solution, you weren't (aren't?) likely to choose MySQL in the first place. But what if you wanted to use PostgreSQL with the Slashcode; would this port be doable in anything like a reasonable amount of time? If done, could it then be shown that the result was any different (for better or for worse) than what you get with the current system?

            Another idea would be to borrow an idea from the functional programming community and do a "database programming contest" like the the annual ICFP Programming Contest. [inria.fr] You could try to find a sponsor and give out cash prizes, or you could get *really* serious (like the ICFP people do) and award year-long bragging rights.

            I mean, both PostgreSQL and MySQL fans would probably go to great lengths to avoid having to refer to the other product as, for example, "the relational database system of choice for for discriminating hackers" in public. Not to mention the possibility that both of them would have to acknowledge the supremacy of DB2 or Oracle or something. :-)

            • > But what if you wanted to use PostgreSQL with the Slashcode; would this port be doable in anything like a reasonable amount of time?

              Yes, I am precisely doing that. The major blocks are only the style of the code and the use of some useless SQL extensions. I cannot say that the systems gain something from the port excluding a more reliable (IMHO) database engine: it could have been useful to use VIEWs and subselects in the Slash code, but the application has been written with MySql in mind - that's the main point.

              If you are interested in Slashcode on PostgreSQL, please raise your voice at Slashcode [slashcode.com].

            • My only problem with comparing MySQL to Postgres is that you can't really use the same SQL code for both. Any sufficiently advanced database is going to have to make use of the non-portable feature-sets (such as auto-increments (sequence v.s. auto_increment)). Datetimes differ (as do maintanance of "last_modified" columns). Database creation scripts differ.

              The above really only means that you can't just drop one database inplace of another (In fact, I've never seen this possible on a sufficiently advanced system. I've gone from Sybase to MySQL to Postgres to Oracle, and at best I could write a low-level portable API in perl to do most of the features IF you used the API instead of SQL).

              But there's a more important point. Different databases have different ways of attacking the problem, and so you'd have to do a complete redesign to properly utilize a given system (and achieve those oh-so important benchmarks). I'm writing an app that's supposed to work on both MS SQL AND MySQL (one for development, and the other for production), but I have to program for the least common denominator (which is mysql). The lack of subquries causes me to rethink the design and write non-optimal code for either system.

              My main point was that Slashcode isn't suited for anything other than a minimalist database, since most other non minimalist databases have proprietary ways of speeding things up (Oracle nested tables, postgres OO selects, Mysql mini auto_increment retrival, etc).

              This isn't to say that slashcode was poorly designed because of it's minimalist approach. That's no different than saying anything that uses berkley file hashes is primative. It does it's job. Fullly SQL-capable just wasn't one of it's requirements. And for some, tape-backed up appended data-sets don't need full data-integrity requirements.

              -Michael

          • Re:Proof, please (Score:3, Interesting)

            by tzanger ( 1575 )

            I find the people here slamming MySQL are often doing so based on their theoretical, rather than practical knowledge. But perhaps you are different. I'd like to see you back up your claim.

            Here [phpbuilder.com] is a recent (MySQL 3.23.26beta and PGSQL 7.1 CVS pre-beta) benchmark. Now I know that benchmarks are the devil's tools, but he really seemed to try and make it a balanced, true-type of benchmark. It is interesting to look at his 1999 benchmark between the two too, where MySQL appeared faster on simple selects and non -concurrent writes to tables. I forget (and don't see it mentioned) if Tim Purdue actually turned off the fsync action that Postgres leaves on by default. If not, it could have explained some of the slowness of Postgres at that time.

            My personal experience with older MySQL is that it is unstable and buggy. We used it for our RADIUS backend for about 3 years and it fell over regularly without much effort. About 18 months ago I replaced it with GNU-RADIUSd and Postgres and -- with four times the load -- it has yet to fail. This wasn't super-high-end stuff either. We're talking about 300 dialup lines with a couple RADIUS daemons making SQL calls to update user logins and time spent when logged out. With MySQL it was 48 lines and a single daemon and I was restarting MySQL so much I wrote a script to do it for me (about five times a week or so).

            I am glad to hear that MySQL works for you; chacon son gout, as the French say (when they don't have accents handy). However based on my experience and the experiences of those who at least appear to be doing unbiased benchmarking, and also based on my need for referential integrity, ACID compliance and robustness, MySQL loses. Hell even those using it for pure speed are losing too, since it isn't the fastest, despite what MySQL, Inc. claims.

    • In other news, PostgreSQL [postgresql.org] announced that it has all the features that MySQL 4.0 has


      Really? When did they add sets and enums, as well as integers with specified storage sizes?

      • In other news, PostgreSQL [postgresql.org] announced that it has all the features that MySQL 4.0 has

        Really? When did they add sets and enums, as well as integers with specified storage sizes?

        Well, "enum" seems kind of handy as an idiom, but exactly the same kind of effect can be gotten with an explicit integrity check in Postgresql. A similar argument (you can implement the idea with, e.g., foreign key constraints) applies to "set". Note that neither of these are generally available in other database products, and both have something to do with referential integrity checks at some level.

        As far as "integers with specified storage sizes" go, I know that postgresql has int2 (same as smallint), int4 (same as int), int8 (aka bigint). Did you mean something else?

  • Does this really warrant a 4.0 release? (Score:5, Interesting)

    by jedrek ( 79264 ) on Tuesday October 16, 2001 @06:53AM (#2435460) Homepage
    I hate to be a spoilsport, but does this really warrant a 4.0 release? I don't really see anything in the changelog that would support a 1.+ release. Maybe a .1+, but not a 1.+.

    Hm... I keep going over it and see stuff like 'Removed all Gemini hooks from MySQL' or 'New character set latin_de which provides correct German sorting'. The only major things I'm seeing right now are the SSL support, support for UNION and boolean fulltext search.

    Am I missing something?
    • I don't think you're missing anything. What bugs me most is that it seems to me like the Gemini table is a feature *enhancement*, yet they're doing everything they can to *remove* it from the distribution.

      I'm waiting till Nusphere releases their copy of 4.0 till I download anything. By then I might have already started looking at PostgreSQL.
      • You should really do it. Look also at the free downloadable postgresql book, which you can also buy. IIRC its from Bruce Momjian(sp?), one of the main developers (if not _the_ main developer) of postgres. Very informative and straightforward that book, also always compares postgres' features agains standard SQL features, so this book is good, even if you don't use it for postgres.

        The tone on the postgres mailing lists is one of the friendliest and most constructive I know of.
    • There's now a command to load tables from master in replication mode... this is really great as having to set up a slave requires you to stop the master, do a backup, load it into the slave, then start both databases... being able to get the slave ready and sync'ed in just one command is great.

  • Windows Frontend? (Score:2, Interesting)

    by Anonymous Coward
    Any idea if anyone is going to release a decent Windows frontend? I must have looked at least a dozen frontends for version 3, and 95% of them fail common usability criteria and all of them are buggier than a shithouse rat.

    MySQL officially sanctions 2 clients: MySQLGUI and MyCC. MySQLGUI falls into both categories mentioned above, and MyCC is still in PRE-ALPHA according to SourceForge even though it was opened almost a year ago.

    And before anyone replies "Have you tried ...", I have exhaustively searched for and evaluated every frontend available on the net, so the likelyhood of a someone suggesting one I haven't tried is very slim.

    • Re:Windows Frontend? (Score:3, Informative)

      by robwills ( 121453 )
      SciBit make Mascon, which is excellent.

      I use their Free Mascon product with my intranet servers and it is robust, quick, many features and a solid interface.

      If I paid for the full version, I could design/alter tables & indexes. Because I don't do that often, I will do it by hand using the cli client or use phpMyAdmin which is pretty cool too.

    • Re:Windows Frontend? (Score:2, Informative)

      by thornist ( 64703 )

      Well i guess I'm walking into your trap, but... have you tried MySQLfront [mysqlfront.de]? I'm guessing you have as it's not obscure, but what didn't you like about it?

      For me it does almost everything I need out of a frontend (and certainly stomps mightily on the piece of shit that is MySQLGUI). And yes I went through all that evaluating too, and MySQLfront came up way on top - edit data, table design, server management etc etc

    • Have you tried Access? From what I hear it is made by the same company that makes Windows, so it might run on that platform(I don't know of any others it runs on). There are also rumors of something called ODBC that let it use other database engines. It supposedly has some nice reporting capabilities too. Unfortunately it isn't open source and costs a few $$$.
    • ... on your windows machine?

      If all you want is a "Front End" I assume to do administration), then it doesn't have to be rocket-fast. an ODBC interface will do fine. Then you can use any windows based SQL tool (Even MS Interdev!)

  • is this news? (Score:1, Flamebait)

    by ragnar ( 3268 )
    Other than the fact that /. uses MySQL for their back end, how is this news? How many incremental alpha releases warrant news mention? This thing got my hopes up, and then I read that it is an alpha release. Yawn. New for nerds with too much time on their hands.

    • Re:is this news? (Score:1, Insightful)

      by guusbosman ( 151671 )
      Of course it is news! MySQL is cool, and for many many Open Source developers it's THE database to use, and 4.0.0 is supposed to bring quite nice new features.

      "too much time on their hands" is a comment which has been made for the whole Open Source movement a lot of times. But it's nice to develop software, it's nice to make Open Source and it's nice to read on Slashdot about new versions of tools we use.
  • From the changelog: 'Removed all Gemini hooks from MySQL.' What is going on here?

    • Re:Gemini? (Score:2, Informative)

      by cheesyfru ( 99893 )
      The Gemeni table handler is produced by NuSphere, which has become demonized for not (properly) GPLing the table type as is required by the MySQL license. There is a FAQ [mysql.com] from the MySQL folks about the dispute.

  • The real reason for this release (Score:5, Insightful)

    by joshv ( 13017 ) on Tuesday October 16, 2001 @07:29AM (#2435538)
    From the release notes:
    "Removed all Gemini hooks"

    See:
    http://www.mysql.com/news/article-75.html

    And you will understand. There probably are not enough changes to warrant a +1 increment in the release number, but this gives them a new version that does not support NuSphere's attempt to usurp MySQL's copyright and trademark rights.

    -josh
    • That's too bad. Despite the litigation, NuSphere was going in a reasonable direction, putting transaction support into MySQL. This new release from MySQL AB doesn't seem to have transaction support from either MySQL AB or NuSphere. That's a lack.
  • Why should a DB admin be interested in MySQL?

  • MySQL+Slash (Score:3, Interesting)

    by JDizzy ( 85499 ) on Tuesday October 16, 2001 @07:47AM (#2435580) Homepage Journal
    I went to this link [mysql.com], and found the name slashdot's senior mysql guy: Brian Aker (aka krow) [slashdot.org]. Seems slashdot has added code for doing better sql dumps in MySQL. If you recall back [slashdot.org] a few several hundred /. stories, you might remember that he also hacked out a method to have stored-procedure calls in MySQL. Also take note that when slashdot upgraded to version 2.1 of their infamous slashcode [slashcode.com], Brian rewrote the schemas for InnoDB style. I'd say that we slashdot folks will see new toys based on some of this new technology because /. is so entrenched with mysql.

    See.......... slashdot really is good for something... =)
  • I couldn't get this version compile on a basic Linux system.
    Everything seems to go very well, but when time has come to link the mysqld program, an unreferenced symbol in InnoDB cause it to tail.
    I tried to disable InnoDB in ./configure switches (just to test because I use InnoDB) . Same thing. MySQL 4.0 doesn't build.
    I tried various flags, tried a vanilla ./configure, etc. Nothing.
    Then I copied files from innodb.com (originally for MySQL 3.23, but... who knows...) . Same result, with more errors and undefined symbols.
    Has anyone successfully compiled MySQL 4 from the source code?

    • Try this configure: (it works for me on my rh 7.1 & 7.2 beta boxes)

      ./configure --prefix=/usr/local \
      --enable-assembler \
      --localstatedir=/var/lib/mysql \
      --with-mysqld-user=mysql \
      --without-debug \
      --without-innodb \
      --enable-largefile \
      --enable-thread-safe-client

      if you want to play with the SSL connection support, add --with-vio -with-openssl to the configure line

      To make sure you have large file support, and good speeds, use something like:
      export CFLAGS="-O3 -mcpu=i686 -march=i686 -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE"
      export CXXFLAGS="-O3 -mcpu=i686 -march=i686 -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE"

      G'luck
      (ps, yes it did compile & run perfectly for me, without any 'hacks', on rh7.1 & 7.2beta)

  • Lets see if they get it right this time (Score:5, Informative)

    by SnapperHead ( 178050 ) on Tuesday October 16, 2001 @08:58AM (#2435826) Homepage Journal
    MySQL has been missing some very important key parts, which makes life very difficault working cross database.
    • timestamp field doesn't follow SQL standards
    • Sub-selects
    • Triggers would be nice
    • select disinct on (field_name) ... would also be nice
    • To sum it all up, I hope they plan on following SQL standards this time around.

  • Open Source DB Mad-Libs (Score:3, Funny)

    by Diomedes01 ( 173241 ) on Tuesday October 16, 2001 @09:09AM (#2435878)

    [MySQL / PostgreSQL / Interbase] is [better / worse] than [MySQL / PostgreSQL / Interbase] because it [has / doesn't have] [row-level locking / transactions / foreign keys / large [development/user] base]

    In all honesty, why can't people just realize that competition is a good thing? Instead of having one database that tries to be all things for all people, the Open Source Community has several database applications available; choose whatever best suits your needs!

    • [MySQL / PostgreSQL / Interbase] is [better / worse] than [MySQL / PostgreSQL / Interbase] because it [has / doesn't have] [row-level locking / transactions / foreign keys / large [development/user] base]

      Hmm...looks like that sentence frame can generate real nonsense like "MySQL is worse than MySQL because it has foreign keys". :-)

      In all honesty, why can't people just realize that competition is a good thing? Instead of having one database that tries to be all things for all people, the Open Source Community has several database applications available; choose whatever best suits your needs!

      Well, as the sage once said "competition does not consist of being different than your adversary, but, rather, of being the same". I think the flame fests really started in this case when mySQL fans made claims about its appropriateness for a very wide assortment of tasks, and then, when challenged, retreat to the line "well, I don't need to do that anyway, and it works okay for me". I think PostgreSQL people then get particularly peeved when somebody who last tried it 3 years ago shouts in public that "PostgreSQL is really slow" without anything recent to back it up.

      Again, what I think we could use here is a fair competition like the one run by the ICFP people. What we have now is a lot of people talking past each other.

  • Hot and incremental backup (Score:3, Interesting)

    by Anonymous Coward on Tuesday October 16, 2001 @09:23AM (#2435927)
    got this from the InnoDB Todo list on their webpage. Look closely to the end of this statement:
    "Hot and incremental backup: you will be able to backup your InnoDB database with a background process without setting any locks on tables and without disturbing the normal processing of your queries. The backup process makes a consistent copy of the InnoDB database, to which you can apply the MySQL binlog when you need point-in-time recovery.
    The backup program will be separate from MySQL/InnoDB, and it will be non-free, with an annual license fee of 250 euros. Please contact Heikki.Tuuri@innodb.com for further information"
  • So a story announcing Samba 2.2 and MySQL 4.0 together, such as here [slashdot.org] isn't worthy of the front page, but MySQL 4.0 alone is?

    Yeesh, guys! You really hate Samba that much? ;)

    I'm kidding of course - only pointing out the duplicate slash article, and heads up on Samba - they do excellent work.

    Also, might I suggest that more people use this [slashdot.org] as their link for slash? It's the best way to catch all the stories.

  • Still on the wrong track! (Score:5, Insightful)

    by MattRog ( 527508 ) on Tuesday October 16, 2001 @02:00PM (#2437385)
    If you're listening MySQL -- who really cares about SSL and unicode. Granted I've had them in my RDBMS that I use but still you're missing the big picture.
    The people who post funny things like 'MySQL Mad libs' are on to something - Slashdot is full of 'arm chair' DBAs (and programmers and network engineers and etc. etc. etc.) who know just enough to think they know exactly what is wrong and how to fix it. It's kind of like the people who have used a computer for a while and start trying to troubleshoot their friend's computers - 'You may have run out of Megahertz' or 'You probably need more memory to download files faster'. Yes, subqueries, locking issues, etc. are very important. However, these are 'surface' level flaws in MySQL. There are some very, very large problems in the underlying architecture which need to be addressed. Since they're not as glamorous (or as understood) as subqueries and pretty much ignored by 90% of the users they never see the light of day - but they impact almost 100% of the users in one way or another.
    Fix things like this:
    Filesystem buffered writes. Transactional support is great - it allows me to roll-back aborted transactions. However, due to the inability to control whether or not my tables are write-buffered means that MySQL may *think* it has performed a write even though it is still in the write-cache. I can then turn off the system and voila -- corruption! Part of the fault lies in the OS who tells MySQL it was written even though it is in the cache, but I have a simple solution. Devise a way to selectively turn off buffered writes for certain tables / databases. This way if I know I have a critical table which has a lot of writes I can turn buffering off and be ASSURED that writes will be performed when asked. I suspect a lot of 1040 and other table corruptions are caused by something like this. Yes, performance will take a hit but I think it is a very acceptable trade-off for data corruption. Obviously all system tables should NOT be buffered.

    Inability to use more than one index on a table in a query -- most enterprise RDBMS' can use more than one index on a table for a query. This can easily save a table scan or the use of a single, less-efficient index. Given an example query - 'SELECT bob FROM sometable WHERE somecol = 45 and somecol2 = 44' - if I have two indexes on sometable (somecol and somecol2) it can join the two indexes together and

    Clustered indexes. These basically physically sort the table based on particular columns. This allows you to ORDER BY username ASC without using anything special since the rows are already sorted on username (if you have a users table and cluster the username col). This also greatly speeds up BETWEEN clauses. And yes, to people who know a little bit of SQL but don't know as much about clustered indexes -- you can create an index with a bobcol ASC but clustering the actual data is faster and more efficient if you are grabbing data which is not on the index. For example, SELECT * FROM table ORDER BY username ASC will not be as efficient as the same query clustered on the username. If you had a sorted index on username it will probably read the index sequentially and then visit the table. That extra operation = more disk seeks = more time / cpu to execute (and it really adds up as the table size increases). However, if you are doing something like 'select username, password from user order by username' it would be better to create a sorted index on username ASC, password. That way it will read the index only and not visit the table at all.

    on-line backups. In today's internet world your site has to be 24/7. This means you cannot have significant performance problems (or even offline-ing your dB!) when you make a dump -- Sybase, etc. have done this from as far back as I can remember.

    backups to something other than CSV files. MS SQL, Sybase, Oracle, they all dump to a compressed binary file. Saves a TON of space and is MUCH FASTER to dump and load. I can dump a 6GB Sybase DB in under 5 minutes. Loading it all (from scratch) and then bringing the DB online is about the same amount of time.

    Ability to specify the number of files to dump to. What happens if you have a dump which is larger than 2GB? Most linux distros cannot handle a single file of 2GB or more. Give users a way to, within the dump statement, split the dump over two files. Sybase has the 'STRIPE ON' clause (originally to dump to two tape drives at once but works fine on filesystem files as well) to split the dump equally over an unlimited number of files.

    cleaner way to view query plans of statements. EXPLAIN ... is great and all, but the resulting table is a PAIN to read. What I want to know is simple:
    Query is using XYZ, ABC tables. Table XYZ is using index 123 which is sorted so I do not need to create a temp table to sort ASC.
    Since you have all the columns in your select statement in the index I do not have to visit the actual table - I can pull it all from the index. Because of this, I will read the index from start to finish.
    ABC is using index 23dsf which is not sorted so I must create a temp table to sort that. Also, since it is a join, I do not need to perform an index scan but a positioned search (table scan is to a WHERE clause with no index AS index scan is to an index which is not selective enough or needs to read all columns.)
    Simple, easy and pretty much even a NOVICE can see that their query is a good performer or a bad performer.
    Along with more in-depth EXPLAIN, also provide me with a way to see what the optimizer is doing with the query. In MS SQL and Sybase you have 'trace flags' which you can turn on before your query to see EXACTLY what Sybase is doing - why does it think this index is better than this other one, why is it table scanning when you think it should index sort, etc. Give me an easy way to say 'verbose on; explain xxx;'.

    Ability to delve deeply into performance of the system. If there is one job a DBA must know it's how to tell what the heck is going on when something is slow. Currently MySQL gives you meaningless info like 'slow queries'. Great, I see 200,000 of them. What queries are they? What good is it in a large application which may contain 3000 lines of SQL to tell me the raw number of queries which are slow? I want to know the EXACT SQL of the query(s) which are slow and I want to find the one taking up the most CPU time and blocking all the rest. I want to know how MySQL is managing it's data cache so I can see if I need more ram (e.g. it is swapping lots of data to/from the cache) or if I am I/O bound. Don't tell me to look at 'free' or 'top' - half the time it is wrong because you (MySQL) tell it misleading figures. I want *you* to tell me exactly what you are doing since you would know best! If you've ever seen a sp_sysmon output from Sybase ASE you'd know what I'm talking about.

    Hire people who are accomplished relational algebra freaks. Pay them a lot of money - it is well worth it!!! If I see another changelog entry like this I'm going to scream:
    Optimized queries of type: SELECT DISTINCT * from table_name ORDER by key_part1 LIMIT #
    So does that mean these queries were NOT AT ALL optimized before? It doesn't read 'FURTHER optimized'.
    "ORDER BY ... DESC can now use keys."
    Does that mean it was table scanning each time? Jebus! Get these wiz-bangs to overhaul your query optimizer. Hands down this is one of the most important things in the database -- knowing how to use the database statistics and knowing when to use a merge-join vs. a hash-join etc. are CRITIAL to database performance. Trust me when I say that this is a MAJOR priority - especially over SSH or Unicode since it impacts EACH AND EVERY statement which MySQL parses.

    Small things which can really raise the respect of MySQL in the eyes of people who matter - the CTOs and CIOs of the world who might be considering something like Postgres or MySQL but opt for MS SQL, Sybase, or Oracle because of the more advanced (but less flashy) features.

    Of course, integrated row (or in the least page) locking and full support of subqueries and the like are also VERY HIGH priorities.

    I think addressing these issues will vastly improve MySQL -- they're not flames really, just gripes :D
    • Clustered indexes. These basically physically sort the table based on particular columns. This allows you to ORDER BY username ASC without using anything special since the rows are already sorted on username (if you have a users table and cluster the username col).

      It seems like most of your problems would be easily solved if we ditched the original set-theoretic background of SQL (where results are in an unordered set as opposed to an ordered sequence). If the DBA was heavily aware of the ordering of tables, then constructing efficient queries that made linear table/index scans would be easier. It seems like this would be amazingly cache and read-ahead friendly.

      -j
    • Postgres has many of the features you are asking for. Their query optimizer is quite excellant for example and the EXPLAIN command gives readable output. They are working on better reporting of the internals but I gather that's not a trivial task.

Slashdot Top Deals

"It is easier to fight for principles than to live up to them." -- Alfred Adler

Close