Posted by
Hemos
on 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."
In other news, PostgreSQL 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
OK, maybe that wasn't announced, but it should have been.
Re:In other news
by
chris.bitmead
·
· Score: 4, Informative
Actually, it's not GPLed, its BSDed. i.e.
nearly, almost public domain.
Re:In other news
by
tzanger
·
· Score: 5, Insightful
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
by
dhogaza
·
· Score: 4, Informative
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.
Re:In other news
by
SuiteSisterMary
·
· Score: 4, Interesting
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.
-- Vintage computer games and RPG books available. Email me if you're interested.
Does this really warrant a 4.0 release?
by
jedrek
·
· Score: 5, Interesting
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?
Transactions, foreign keys
by
blackcat++
·
· Score: 5, Informative
I hate to disappoint you but transactions, foreign keys and row-level locking are available and seem to work quite well.
The real reason for this release
by
joshv
·
· Score: 5, Insightful
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.
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.
Re:But why?
by
killmenow
·
· Score: 4, Informative
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 (or Firebird 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?
by
micromoog
·
· Score: 5, Informative
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.
Lets see if they get it right this time
by
SnapperHead
·
· Score: 5, Informative
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.
-- until (succeed) try { again(); }
How about a competition? (was Re:Proof, please)
by
King+Babar
·
· Score: 4, Insightful
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. 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.:-)
--
Babar
Stupidity runs rampant in our industry
by
Morocco+Mole
·
· Score: 4, Interesting
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
Still on the wrong track!
by
MattRog
·
· Score: 5, Insightful
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
Slashdot Mirror
In other news, PostgreSQL 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
OK, maybe that wasn't announced, but it should have been.
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 hate to disappoint you but transactions, foreign keys and row-level locking are available and seem to work quite well.
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
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.
Umm, how about Interbase (or Firebird for that matter)? Definitely qualifies as "major" IMO, is being actively developed, and runs on Windows, Linux, Solaris, and others...
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."
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.
until (succeed) try { again(); }
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.
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. 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. :-)
Babar
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
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.
... is great and all, but the resulting table is a PAIN to read. What I want to know is simple:
... DESC can now use keys."
:D
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
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
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
Thanks,
--
Matt