Categories

Main
Database
Java
Microsoft.NET
Adabas
DB2
Informix
Microsoft SQL Server
MySQL
Oracle
Pervasive.SQL
PostgreSQL
Sybase
Other
ASP
ColdFusion
Crystal Reports
Delphi, C etc
JAVA
Microsoft.NET
Perl and the DBI
PHP
ANSI SQL
Unix Shell Scripts
Visual Basic
XML & XSLT
Corel Paradox
FileMaker
Microsoft Access
Microsoft Excel
Other PC Databases
Applications & Tools
Database Concepts & Design
EJB programming & troubleshooting
EJB design
General J2EE
XML & Web services
Web tier: servlets, JSP, Web frameworks
Performance and scalability
Industry news
TSS feedback
Mobicents Contributors
Mobicents Users
JSLEE Resource Adaptor Types
Planning JavaOne 2008
Sun Tech Days
Other Java conferences
Binary Web Services and XML
Metro and JAXB
GlassFish
GlassFish Plugins
Project jMaki
GlassFish WebTier
Mural
Java Development Tools
Java WS & XML Community News
JAXP
Java SE
6uN Early Access
Java Quick Starter
Java SE Snapshots: Project Feedback
JCK Forum
Feedback and Suggestions
JDK Distros
JDK Open Source
General JSR Discussion
JCP 2.6
JXTA Community Forum
ME Interest
ME Cool Apps
ME General Help
ME Feedback & Suggestions
ME Application Developer Interest
Blu-ray Disc Java
ME Developer Days
Squawk
Mobile Developer Alliance
OpenCable
LWUIT
JavaFX Script Language Discussion
OpenJFX General Discussion
Scene Graph
General Performance Discussion
Your Java Career
NetBeans 6.0
Servlets
JSP
JSF
Portals and Portlets
EJB and Other Java EE Technologies
Distributed Java
Object Relational Mapping
JDBC
Web Services
Swing / AWT / SWT / JFace
JNLP and Web Start
Java Micro Edition
Sockets and Internet Protocols
Threads and Synchronization
Performance
Applets
I/O and Streams
Other Java APIs
Game Development
Java in General (beginner)
Java in General (intermediate)
Java in General (advanced)
Programmer Certification (SCJP)
Developer Certification (SCJD)
Associate Certification (SCJA)
Web Component Certification (SCWCD)
EJB Certification (SCBCD)
Mobile Application Certification (SCMAD)
Architect Certification (SCEA)
Web Services Certification (SCDJWS)
XML Certification
Product and Other Certifications
Mock Exam Errata
Sun Certification Results
Authors' Corral
Book Reviews
Events
Bunkhouse Porch
Teachers' Lounge
Testing
OO, Patterns, UML and Refactoring
IDEs, Version Control and other tools
Ant, Maven and Other Build Tools
Linux / UNIX
Mac OS
HTML and JavaScript
XML and Related Technologies
Agile and Other Processes
General Computing
Security
Groovy
Scala
Other Languages
Struts
Application Frameworks
Other Open Source Projects
BEA/Weblogic
IBM/Websphere
Oracle/OAS
Apache/Tomcat
JBoss
Other Java Products and Servers
JavaRanch
Cattle Drive (java college)
Moderators Only
Trash Can
Jobs Offered
Jobs Wanted
Jobs Discussion
Meaningless Drivel
Programming Diversions
Blatant Advertising
Java Announcements
New To Java
Advanced Java
Java Applets
Networking
Threads and Synchronization
Java 2D
AWT / Swing
SWT / JFace
CLDC and MIDP
CDC and Personal Profile
Sun Java Wireless Toolkit
Enterprise JavaBeans
JavaServer Pages (JSP) and JSTL
Java Servlet
JavaServer Faces
Web Frameworks
Database
XML
Lucene
NetBeans
Eclipse
IntelliJ IDEA
JCreator
Other IDEs
Java Tutorials
Java Tips
Jobs Discussion
Jobs Offered
Jobs Wanted
Professional Certification
Forum Lobby
Java Blogs
Introductions
Reviews / Advertising
Suggestions & Feedback

Resources

Java Database
Linux
Coding
Mobile
Hardware
Software Development
Software Development
iOS,OS X
iOS,OS X
ORACLE
IBM DEVELOPER
IBM DEVELOPER
MSDN
MSDN


Tags

MySQL

MySQL is the cross-platform open source database server software used extensively in web development and implementaion. It lacks many advanced features that are taken for granted in the enterprise systems, but these shortcomings are offset by ease of use and impressive speed.

MySQL vs Firebird? ( 1 2)


Hello everyone,

I'm currently working on a medium-sized website, including a store and 'regular' news / article / commenting features. The company me and my team are creating this site for also has a real-life store, which will have software running on it's consoles that queries the same database used by the website, for product information and such.

Up until now we've been developing the website using MySQL as a database solution, but recently the team that's developing the software for the real-life store has brought up the idea to switch to a database server called Firebird - http://firebird.sourceforge.net/
I couldn't find any benchmarks or featurelists on that website, nor could I find feature comparisons and benchmark-comparisons with Firebird on the MySQL site. Ofcourse I'm dying to know which database is the better choice. The team that's developing the software claims that Firebird is more reliable when there are a lot of users quering the database.

Could anyone tell me more about the cons and pros of Firebird in relation to MySQL? Thanks in advance!

p.s.: English is not my native language so I apologise for any errors

Quote:
	
	
		
			
				Originally posted by Victorious 
Hello everyone,

I'm currently working on a medium-sized website, including a store and 'regular' news / article / commenting features. The company me and my team are creating this site for also has a real-life store, which will have software running on it's consoles that queries the same database used by the website, for product information and such.

Up until now we've been developing the website using MySQL as a database solution, but recently the team that's developing the software for the real-life store has brought up the idea to switch to a database server called Firebird - http://firebird.sourceforge.net/
I couldn't find any benchmarks or featurelists on that website, nor could I find feature comparisons and benchmark-comparisons with Firebird on the MySQL site. Ofcourse I'm dying to know which database is the better choice. The team that's developing the software claims that Firebird is more reliable when there are a lot of users quering the database.

Could anyone tell me more about the cons and pros of Firebird in relation to MySQL? Thanks in advance!

p.s.: English is not my native language so I apologise for any errors 
			
		
	
	
 

Here's a quick list of what I could come up with...

MySQL has a much larger community and support than Firebird has.   

Firebird *terribly* lack good ODBC support...  None of the drivers I've tried so far worked...  And the ones that worked "best" were pretty expensive.

If you don't need transactions (e.g. just feed your website for display from MySQL), the only known database that is faster than MySQL is Oracle...

MySQL is supported on a zillion platforms.

MySQL has lots of documentation for PHP, ASP, etc support...

You'll find more people that know MySQL than Firebird specialists.

If you're doing lots of DELETE, performance on Firebird will degrade a lot faster than MySQL (see sweep operation).

BTW, I am not doing propaganda for MySQL, I'm a DB2 fan!

totally agree

and i would recommend mysql for all who ask me for an extremely fast, reliable, easy to manage, and most valuable: you can use it for FREE if you want.

Where MySQL wins: nice full text indexing, and, as others have pointed out, it's ubiquitous. It has a few SQL extensions that you'll wish had made it into the standard like "REPLACE INTO." It's nice to use a database that, if you type "show tables" will just show you a list of your tables.

Where it loses: it's unreliable. Not in the sense that it's liable to crash, but in the sense that it provides weak foreign key constraints, has no triggered procedures and transaction-safe tables are an extension.

That means your integrity checking has to be strewn all over your application. Not that redundant integrity checks shouldn't be in your application, but the DBMS should be the failsafe point. You should also be certain that data in the DBMS is correct, even if something goes wrong with the app.

Moreover, the fact is that most MySQL developers don't know how to write integrity checks properly. Let's face facts: these are people who are hacking together a website (that's what I use it for...) and probably wouldn't know of any monitors beyond the one on their desk. Structured exception handling... wow, that's really fun to write, and did you actually do test cases for it?

Proper exception handling requires a degree to understand let alone code, it's incredibly painful to code, and it protects against glitches that rarely happen. Even if you could, why on earth would you ever want to code it when you could let the DBMS take care of all that? You may as well just let the DBMS throw an error and pass it back to the user. Check the error codes for common problems and just tell them it's an unexpected error the rest of the time.

Speed: with MySQL the only way to get proper transaction handling is through InnoDB tables. (And these have all kinds of restrictions on them.) In a real life database, one to which you would actually entrust your valuable data, all those supposed speed benefits go up in smoke.

The only way to be able to do live backups is with InnoDB. Again, if you're doing a business website, live backups is not optional.

MySQL doesn't have stored views or sub-SELECTs. It won't, according to the authors, until someone writes it for them or pays them to do it. What does that mean for you? You get to write lots of iterating loops which will bloat your code making it buggier and harder to update.

Inconsistent table types: There are 6 table types, and the semantics are different for all of them. Example: foreign key constraints only work between InnoDB tables, and full-text indexing only works on MyISAM tables.

Stored procedures: They have to be written in C++. That means that all that iterating you have to do (because there are no sub-SELECTs or stored views) is going to result in repeated round-trips. You can kiss network transparency goodbye.

Anyway, as with any DBMS, there are plenty of minor issues and it carries all the baggage of SQL. These are the problems that prevent the use of MySQL in anything but quick and dirty hacks.

you're right, but all things always have pros and cons. no person is perfect, so does the software. if one wants Unix in nice GUI, it wont be that stable, same thing as with windows thats never been as reliable as Unix in stability.

thats why people want a football player with the brain of Zindane, the left leg of Roberto Carlos, right leg of Ronaldo, head of Keane, the (God) hand of ... Maradona.... and more!!!

but how?

Quote:
	
	
		
			
				Originally posted by sco08y 
Speed: with MySQL the only way to get proper transaction handling is through InnoDB tables. (And these have all kinds of restrictions on them.) In a real life database, one to which you would actually entrust your valuable data, all those supposed speed benefits go up in smoke.

The only way to be able to do live backups is with InnoDB. Again, if you're doing a business website, live backups is not optional.

			
		
	
	
 

Well, I have to disagree. I work for a company that processes in excess of $500 million annually and uses MySQL 3.23.5x (haven't upgraded all the servers to 4.0.x yet). We have never used transactions (found very little use for them actually). MySQL has never crashed on its own. We had 1 incident where the hard disk filled up due to other process logging and MySQL basically bombed.

As for live backups... we do it currently. A simplistic solution is to set up a Master and a Slave. Take the Slave down every now and then to move the data off site, start it back up and it gets all the data it missed from the master. If you do this once an hour, you lose an hour's worth of data at most.



	Quote:
	
	
		
			
				Originally posted by sco08y 
MySQL doesn't have stored views or sub-SELECTs. It won't, according to the authors, until someone writes it for them or pays them to do it. What does that mean for you? You get to write lots of iterating loops which will bloat your code making it buggier and harder to update.

			
		
	
	
 

Sub-selects are in version 4.0.x. Views will be in 5.x. Full foreign key support will be in 5.x

These are all overhead in my opinion. In 5 years of coding/designing transaction processing systems with MySQL, I've had very little need for sub-selects or views. I think these are over-used.


	Quote:
	
	
		
			
				Originally posted by sco08y 
Stored procedures: They have to be written in C++. That means that all that iterating you have to do (because there are no sub-SELECTs or stored views) is going to result in repeated round-trips. You can kiss network transparency goodbye.

			
		
	
	
 

You can write UDF's in C. I saw a version someone hacked together to be able to write them in Perl. Not sure if that ever got developed further though. Probably just proof of concept stuff, but it worked within the limits of Perl 5 (threading is poor but is required for MySQL UDFs).

As for repeated round trips. Version 4.1.x supports prepared statements within the C API (not sure if Perl's DBD::mysql, or PHP supports these calls yet) . It also supports the sending/returning of multiple queries/result sets at one time.


	Quote:
	
	
		
			
				Originally posted by sco08y 
Anyway, as with any DBMS, there are plenty of minor issues and it carries all the baggage of SQL. These are the problems that prevent the use of MySQL in anything but quick and dirty hacks.

			
		
	
	
 

Like I said, I use it at work for some pretty major processing. We have sub-systems that take over 3 million transactions daily. One system in particular takes 61 questions per second every single day.

Still running good.

Sub-selects are in 4.1, which is still in alpha, unlike 4.0 which is stable. (Again, this highlights the need for fact-checking when talking to MySQL fans!) And 5.0 is, at this point, vapor-ware.

As for your big system, true, you can still do massive systems that rely on '60s technology, just like they did in the '60s. Smart money says you'll upgrade to Oracle when upgrades start to come in late because your team is ferreting out every last integrity check that's strewn throughout the application code becomes prohibitive.

Quote:
	
	
		
			
				Originally posted by sco08y 
As for your big system, true, you can still do massive systems that rely on '60s technology, just like they did in the '60s. Smart money says you'll upgrade to Oracle when upgrades start to come in late because your team is ferreting out every last integrity check that's strewn throughout the application code becomes prohibitive. 
			
		
	
	


Umm, C is "60's technology." What do you think most other languages are built off of?

Besides I'm not against other databases. They have their uses. For what I do, MySQL works and it works well. You, however, sound fairly biased against it.

I just wanted to point out that your views of MySQL are not correct. It can, and does, work on a large scale. We've been running MySQL for 5 years in transaction processing with few problems. Maintenance is extremely simple. Plus, we don't have to pay the $1 million+ that Oracle would cost.

There are a few things I wish MySQL had, but nothing that can't be worked around with some good system design and ingenuity.

Quote:
	
	
		
			
				Originally posted by kellewic 
Umm, C is "60's technology." What do you think most other languages are built off of?

Besides I'm not against other databases. They have their uses. For what I do, MySQL works and it works well. You, however, sound fairly biased against it.

			
		
	
	


Yup. C is 60's technology, and that's a great analogy for describing the weaknesses of it. Think about all those problems you have to design around in C: buffer overruns, problems with void*, spaghetti coding. Sure, you can write great software in it and sometimes the more advanced stuff (e.g. Java) isn't up to task and you *have* to use it, but you shouldn't dismiss the weaknesses.

I'm biased against *all* SQL DBMS's. I want a true relational DBMS, and I'm quite happy to criticize vendors as long as they come up short. And, as you pointed out, they do listen. That's why, for example, the MySQL team is working on sub-selects and views, and has added transactions.

BTW... has anybody actually used Firebird or Innobase? Can the original poster (who seems to have gone AWOL) check the criticisms I made of MySQL and post his findings back to the board?

Hi all, thanks for all the replies, quite helpful.
I haven't gone AWOL, whatever that means, just been busy.

We still haven't decided what database to use, mostly because both teams would really like to stick with 'their' database, and none are really experts, which is why I turned to the users of this board, among other people.

My team would like to use MySQL, simply because we've never used Firebird and therefore feel more comfortable developing in a MySQL environment. The same probably goes for the other team and Firebird. However, if Firebird would indeed be the best choice, we'd ofcourse switch.

In this thread a lot has been said about MySQL, but not much has been said about Firebird. Could anyone give me some insight on how Firebird compares to MySQL?

None of the involved team are real database experts, so I thank you in advance for any further replies.

Quote:
	
	
		
			
				Originally posted by Victorious 
Hi all, thanks for all the replies, quite helpful.
I haven't gone AWOL, whatever that means, just been busy.

			
		
	
	


Absent Without Leave, used in a satirical manner.


	Quote:
	
	
		
			
				In this thread a lot has been said about MySQL, but not much has been said about Firebird. Could anyone give me some insight on how Firebird compares to MySQL?

None of the involved team are real database experts, so I thank you in advance for any further replies. 
			
		
	
	


Honestly, your best bet would be to google Firebird and read the documentation. Or check their mailing list.

If you find something interesting, do post it here.

Quote:
	
	
		
			
				Originally posted by Victorious 
We still haven't decided what database to use, mostly because both teams would really like to stick with 'their' database, and none are really experts, which is why I turned to the users of this board, among other people.

In this thread a lot has been said about MySQL, but not much has been said about Firebird. Could anyone give me some insight on how Firebird compares to MySQL?

None of the involved team are real database experts, so I thank you in advance for any further replies. 
			
		
	
	
 

please remember that this is mysql area, experts of one db have little chance to be experts of others, havent it? you should try firebird forums for more opinions

cheers

In case you haven't made a choice yet, I'll post my opinion.

I'm a firebird user -- I'm the DBA (when needed, which is not often) and the tech-lead for the team. I'll vouch for firebird in the places I've used it -- we access it from a fat client (c++/win32) at my main job, and I've used it with php several times for websites (in fact, helped report problems to the php team when ibase_* functions were having issues.)

It's reliable. It's stable. It's fast. It's got the features you will likely need. And it's generally "correct" in the logical sense: transactions aren't just a random feature. They're part of relational theory, when it comes to a multi-user database system. If it's interactive, if it's mission-critical data, if it's a multi-user system ... go with something designed for those. Postgresql, firebird, SapDB, ... find whatever you like. But please don't use mysql. (all three mentioned are free.)

(and yes, a web-app SHOULD use transactions to make sure semi-simultaneous page hits don't run into problems because of updates occurring somewhere in the database, whether it's from the web, or from an internal database feeding stuff to it. and a db like firebird where transactions are part of the very essence of the database system, not an add-on ... means that if your power fails, or something else goes wrong, your db will still be in a perfectly valid state, ready to go again. it's a bit like the difference between fat32 drives and ext3 or reiserfs -- don't you want your data to be safe?)

Quote:
	
	
		
			
				Originally posted by unordained 
In case you haven't made a choice yet, I'll post my opinion.

I'm a firebird user -- I'm the DBA (when needed, which is not often) and the tech-lead for the team. I'll vouch for firebird in the places I've used it -- we access it from a fat client (c++/win32) at my main job, and I've used it with php several times for websites (in fact, helped report problems to the php team when ibase_* functions were having issues.)

It's reliable. It's stable. It's fast. It's got the features you will likely need. And it's generally "correct" in the logical sense: transactions aren't just a random feature. They're part of relational theory, when it comes to a multi-user database system. If it's interactive, if it's mission-critical data, if it's a multi-user system ... go with something designed for those. Postgresql, firebird, SapDB, ... find whatever you like. But please don't use mysql. (all three mentioned are free.)

(and yes, a web-app SHOULD use transactions to make sure semi-simultaneous page hits don't run into problems because of updates occurring somewhere in the database, whether it's from the web, or from an internal database feeding stuff to it. and a db like firebird where transactions are part of the very essence of the database system, not an add-on ... means that if your power fails, or something else goes wrong, your db will still be in a perfectly valid state, ready to go again. it's a bit like the difference between fat32 drives and ext3 or reiserfs -- don't you want your data to be safe?) 
			
		
	
	
 

Well, MySQL handles transaction now with the InnoDb handler (which comes standard now).  So there's not problem on that  side and for read-only stuff, nothing is as fast as MyISAM tables (expect Oracle).  The only thing missing now are views...  But it's coming soon...

Quote:
	
	
		
			
				Originally posted by bstjean 
Well, MySQL handles transaction now with the InnoDb handler (which comes standard now).  So there's not problem on that  side and for read-only stuff, nothing is as fast as MyISAM tables (expect Oracle).  The only thing missing now are views...  But it's coming soon... 
			
		
	
	


Ahem, transactions work *only* if all tables involved are InnoDB. Most of the other features only work on MyISAM tables. Since the only way to use MyISAM safely is with locks, the speed advantages are false.

And, please, I've heard enough promises about all the wonderful features that are "coming in the next release... we promise!" MySQL will offer views... but will they be crippled the way transactions are? What limits will there be on updatability? Will they only work on certain table types?

4.1 is still alpha, and you act like a 5.0 beta is out. I don't demand that everyone be a ruthless skeptic, but at the same time, some of you guys sound like PR flacks.


Related Links

Need a little help with mySQL
Concat returns BLOB
how to install maxdb in red hat linux?
String Manipulation within a query
Mysql ODBC
exposed password?
Find Last Status
How in the HELL do I do this!?!?!?!?
How to know if a table or column exists
how to get the auto increment integer primary key value of a new record
Will .asp work with mysql?
List of tables in my database
Max Table Sizes in MySQL 4 & 5?
COUNT period btw 2 date time at SELECT statement
Convert Foxpro to Mysql
parse a column