Navigation
User login
Quote
MySQL comes of age, 4.0 production ready
MySQL has always been my personal database of choice for web applications. However, in the past it did have some serious limitations. Most notable among these issues were foreign keys, row locking capabilities, subselects, etc. For a basic web app that could get by without those features, or with a subset or workaround, MySQL was always great. For more complicated things a more serious, and more complicated database was needed (ie PostGRES or Oracle).
Well now apparently MySQL 4.0, which just yesterday was declared "production ready" has all that jazz. You name it, its in there, foreign keys, yep, row locking, yep, etc, etc, etc. I havent used MySQL in anything other than a simple context, but for speed and ease of use it has always been great. Maybe its time to try 4.0 in place of PostGRES or Oracle?
For more on 4.0 see the linked press release from MySQL. MySQL 4.0 Production Ready
TotSP Search
Community
Chatter
- $400 for tickets thats just
2 days 9 hours ago - about:Chrome
3 days 10 hours ago - Chrome Tubes
3 days 11 hours ago - Phones are imminent, I get
6 days 5 hours ago - Before writing a crappy appp
1 week 2 days ago - Another good option to add mail handling to your tests...
1 week 2 days ago - There are caveats, too
1 week 3 days ago - warp-widgets + warp-persist
1 week 5 days ago - Yes, seems pretty handy
2 weeks 4 days ago - Cool
2 weeks 4 days ago
Comments
Re: MySQL comes of age, 4.0 production ready
Not to knock this too much, but what kind of production? They still don't support stored procedures. Every high-transaction production system I've ever seen uses stored procedures.
Re: MySQL comes of age, 4.0 production ready
I agree - stored procedures is the baseline. If you don't have that your not even up to the starting line yet.
In the mean time everyone else is moving ahead... where is support for clusters?? EA is running Sims on 4 Node Dual CPU Redhat clusters under Oracle 9 with rates of 30,000+ SQL Calls per second into a multi-terrabyte db. Redunant, scaleable...That's what excites me now...row level locking?? bfd.... If only it didn't cost 5 million dollars per node...
Re: MySQL comes of age, 4.0 production ready
Well, the whole Store procedure/Lock thing seems like window dressing to me for MySQL. It was really designed to be big and fast, not necessarily your transactional DB. For instance, NASA uses it to archive flight data from probes because it can handle storage and quick retrieval of large quantities of data very well, and they don't need all those fancy things -- they will pull the data and analyze it in something more suited to the task.
MySQL is really the Red Brick or Teradata of open source databases.
Re: MySQL comes of age, 4.0 production ready
Well I would diverge here. I think stored procedures are one of those things that get a bit overhyped (I said a bit, calm down). I of course could be wrong but I will let you guys educated me. (I dont use them very much, not even on what I would call very LARGE scale projects, ie a webstore that does over a million dollars worth of volume every month that I am currently working on).
A stored procedure is better because? Its pre-compiled and therefore faster? And maintenance is easier? Again please educate me.
I would say that the first is a big advantage, if the pre-compilation makes them a great deal faster then yeah, they are hella worth it. However to the second part, easier maintenance I say bah.
I always put my SQL in a properties file (well, ok, not always, but by the time something is called done thats where I want the sql to be) and its called from within code, so its VERY easy to maintain. You dont recompile the code, you just change the text file. Thats pretty darn easy on the maintenance.
Also, stored procedures are planned for MySQL 5.
Re: MySQL comes of age, 4.0 production ready
Off the top of my head:
Stored procedures are precompiled, which is a great benefit for high-transaction, repetitive, and complex queries and the like.
They also allow complex behavior within the DBs process space so that you don't have to do a select, then parse it back on the client, then do more select based on the data in the first. This reduces the number of connections, (the best benefit?) its presumably faster, etc.
As for maintenance, you are 100% correct, based on my experience. Every RDBMS has a different syntax for SP, so if you want to maintain different DBs, you have to maintain this code twice. Not pretty.
Also, it's a lot more resource intensive to read in a text file everytime you wanna do a query. That's something that may or may not be possible based on your application.
The product I work on now does not use stored procedures, and in most cases, that is OK. There are some cases however, where SP would have made everything much easier and better. We're using RDBMS that have SP, but the management has seen fit not to use it. In fact, we do something similar to what you describe in that all of our SQL statements are stored on the file system. I guess this is better than in the java code itself, but it is clunky at best.
Re: MySQL comes of age, 4.0 production ready
I use stored procedures whenever I need to perform multiple inline queries based on results from prior queries - I don't have to pay for the round tripping back and forth from the server. I must admit for some things it's not that much faster, but it sure seems cleaner to me. I also like being able to make inline changes to SP's without having to stop all the programs accessing that data (really nice for some of my apps that are running on 20 machines in 2 countries hitting the same database) I make a change to the SP and all the apps automatically are running the updated version.
Re: MySQL comes of age, 4.0 production ready
agreed, you all make great points.
i need to learn more about and use SPs, but for now its still pretty clean (I think, even though mutt calls it "clunky") to have the SQL in props files and the code read that. the performance advantage I cant argue with though and thats reason enough that, for once, you guys are right.