Helpful Information
 
 
Category: Firebird SQL Development
MySQL -vs- Firebird SQL

Does anyone know the difference between these 2 databases? I'm particularly interested in features it has, performance and support by PHP. If I'm not mistaken one can connect to firebird by using the InterBase (http://www.php.net/manual/en/ref.ibase.php) functions ?!?

Thanks for any information/links/whatever! :)

Yes - you would use the ibase functions to connect to, insert and gather your results from PHP.

As for the difference between the two, Firebird is extremely feature rich (mysql isn't even in the same ballpark). Switching from MySQL to Firebird is a big leap forward.

This is the full set of docs in pdf format (10 or so megs): http://www.ibphoenix.com/downloads/60All.zip

Wouldn't some of those features slow down the queries for simple things? I mean maybe I am wrong... but even having the ability to add in triggers and other nice features like that, in theory, would at least slow down simple queries that were not even tied into triggers right? Maybe I am wrong on the select side but it seems on the insert or update side there would have to be some more things going on no?

Wouldn't some of those features slow down the queries for simple things?

Well, only if you are using them. There are numerous advantages to having features as stored procedures, triggers and events available. To give you an example, I am currently working on a large application that will be compatible with three different databases (FB, PG, MySQL); the application needs to keep logs of all database INSERT and DELETE statements. MySQL forces me to do this on the application side, which in turn creates quite a bit of extra work:


/*------------------------------------
Insert Data Into Table
------------------------------------*/
"INSERT INTO products VALUES('','etc','etc','etc')";
/*------------------------------------
Insert Data Into Log Table
------------------------------------*/
"INSERT INTO logs VALUES('the log data')";

Whereas with db's that support triggers and such can fire off an event when the one INSERT statement (the first one above) is executed and thus execute the stored SQL code, effectively inserting the log record into the log table. BTW - sql code is faster than application code.

So now my library files may look like this:


object.mysql_product_engine.rfile
(1500 lines of application code)
object.fb_product_engine.rfile
(1000 lines of application code)
object.pg_product_engine.rfile
(1000 line of application code)


For me, this creates a little problem; the code varies quite a bit and in the end; makes it harder for me to create my documentation files - example:

Developer Docs - FB and PG Product Engines
Developer Docs - MySQL Product Engine

As opposed to:

Developer Docs - Product Engines (would cover all three)

------------------------------------------

Maybe Rycamor or Rod will bless us with there database knowledge and give us some better examples... :)

well in theory you could abstract all the database calls and just pass the database calls to a DB object which would then do the db stuff based on the database you are dealing with and if it is MySQL write out the insert and delete statements... but yes I see what you mean.

I had read that just having the features would slow it down even if you did not use them which seems a little silly but at the same time not. I certainly agree, in your case here, that having triggers helps the developer a lot.

Now I never actually tested any of this, I was just listening to what others have said. I have always used MySQL on PC type servers and Oracle on the boxes that cost 250k.

Originally posted by md_doc
Wouldn't some of those features slow down the queries for simple things?
...but it seems on the insert or update side there would have to be some more things going on no? Q: Is the trigger doing an essential piece of work?
A: Yes. Otherwise you wouldn't have coded it.

Q: If the trigger doesn't do this essential work what does?
A: The application does.

The problem with the application handling the work done by the trigger is that this involves more roundtrip traffic across the network as the program does a series of discrete steps. Compare this to a single call across the network which fires off a series of server-side actions. The same amount of work is getting done but the trigger method is more efficient. You can also gain a performance benefit from the database server already having preparsed the trigger. The server will already know what the most efficient query plan is. If you allow the application to handle the trigger step via an SQL call, you can incur the overhead of the server having to parse the command and determine a query plan.

Another advantage of triggers is that it prevents a user from bypassing business logic by using an ad-hoc query. If you do an insert from the command-line, the trigger and its logic gets run. If the trigger logic is in an application, then these steps don't get run by SQL commands outside the application.

Stored procedures (SP's) offer many of the same benefits. I do a lot of fat client programming. If you keep your forms lightweight by putting the business logic server-side in an SP, you can change your business logic in one SP and every application gets it. If an application tries to submit bad data, the SP will raise an error that is returned to the client explaining what is wrong. You've made a "code change" without having to push X number of client applications out to workstations.

Some people use SP's as an added layer of security. The DBA's don't give users or programmers access to the tables directly. They just give access to the SP's which forces the associated code to be run whenever the data is manipulated. You can also make your application code very portable using SP's. Even if SQL syntax is different in Oracle, DB2 and Firebird, as long as I put the SQL in a SP with the same name on each database, my application code doesn't have to change.

The good thing about databases like Firebird is that I never have to use any of these features. But when certain problems arise, they're nice things to have in my toolbox.

Lol - I will now change my previous reply a little

<me>Erasing last line of text in my last reply</me>

<me>Writing the new line:
Maybe Rycamor, Rod or dcaillouet (nick named dman) will bless us with there database knowledge and give us some better examples...:)
</me>

Kudos to dcaillouet for a nice clear explanation.

I will only add that there can be performance benefits to using stored procedures. There's not always a performance downside:

Stored procedures are usually compiled, cached functions, so one SP can often perform a group of related queries faster than if you 1. begin a transaction, 2. execute your queries, and 3. commit the transaction. And, as dcaillouet mentioned, those queries are all happening in one environment, atomically, rather than travelling to the SQL system from the app environment. So, when performing a complex operation, you make one DB call from your application environment rather than 4 or five, for example.

In fact, even if you just use a stored procedure to handle one query, that might be faster than a standard application SQL query, because the SQL query plan itself can be cached.

Also, as to the question of database features, it's funny to me how often programmers immediately raise the red flag about performance. The amount of application code you save by having advanced logical features is amazing, once you get the hang of it. There are just some things that make so much more sense in a relational query mode. (not everything, I understand). For example views, procedures, CHECK constraints, and domains-- when you get the feel for these, you will be amazed at the coolness factor. These go a long, looooong way toward making a database management system feel more "object-oriented". Each of these allows for a powerful type of encapsulation, and even a sort of inheritance, if you learn to play with them. (And the future looks even better. With the more advanced RDBMS systems that www.thethirdmanifesto.com talks about, inheritance and abstract datatypes will be built into the system from the ground up).

So don't view these features as "trade-offs to performance". View them as ways to accomplish more with less work. Programmer performance is worth far more than system performance ;-).

So, when performing a complex operation, you make one DB call from your application environment rather than 4 or five, for example.I went to an IBM developerWorks conference in New Orleans last month. One of the speakers was talking about a client of his that had a transaction with ~80 steps that needed to complete before it was finished. Ouch! Some people have pretty demanding requirements out there.

Fortunately a lot of the systems I'm currently working on are fairly small (10's of gigs). On these I'm always willing to give up a few milliseconds of performance to have the constraints, checks, domains and other server-side objects that make database programming more robust.

I like Firebird simply because it runs on all the platforms I program on and does everything I need really well. Also I find it easy to maintain. Fortunately for me, with release 8.1, DB2 has become a lot easier to work with. Still, I'd rather work with Firebird.

Of cause the main advantage of Firebird is the lack of any Licencing considerations if you are using it comercially.

I run local copies on all sites, and replicate from the master site, without having to worry :)

Let me add my two bits into this debate


Comparing MySQL with Firebird is like comparing dBaseIII with Oracle...



True MySQL offers great speed and has a wider market share, but it is suitable only for Web-Applications and single user applications...

Let me compare the features

MySQL Firebird
---------------------- --------------------
SQL-92 compliance very small subset most of it
Relational Integrity new in V4.0 Yes
Stored Procedures No(plan for V 5.0) Yes
Triggers No(plan for V 5.0) Yes




As for Performance, MySQL will outperform Firebird on almost all tests on local machine......

On a Network Server a properly designed Firebird Server will outperform MySQL on any Test and the network-Traffic generated by Mysql will be many times higher than Firebird

So you decide the one you need according to your needs .....

Whoops!!!! I see this forum reformats my reply.. so the feature comparison is as follows


MySQL
----------------------
SQL-92 compliance ----- very small subset
Relational Integrity ----- new in V4.0
Stored Procedures ----- No(plan for V 5.0)
Triggers ----- No(plan for V 5.0)



Firebird
----------------------
SQL-92 compliance ---- most of it
Relational Integrity ---- Yes
Stored Procedures ----- Yes
Triggers ----- Yes

PostgreSQL
----------------------
SQL-92 compliance ---- most of it
Relational Integrity ---- Yes
Stored Procedures ----- Yes
Triggers ----- Yes

What are differences between Firebird and PostgreSQL? Why are there many people still using MySQL? What is the criteria to select one amongst three of them?

Why are there many people still using MySQL?It is easy to use and setup. Its been around for a long time so there are a lot of open source projects that use it. Plenty of support available on the web. A lot of people don't need or can work around the features it is missing. Years ago it was faster than Postgresql and it ran on Windows as well as Linux so people favored it. These last two points are moot now.


What are differences between Firebird and PostgreSQL?Not much. They both have a ton of features and either would be a good choice. There's subtle differences between a BMW and an Audi but they're both a better choice than a Hyundai. Same with Postgresql and Firebird vs. MySQL. Both databases have features that MySQL doesn't. This is an incomplete list: Views
Triggers
Stored Procedures
Event managers to notify clients of database actions.
Column level constraints
User can create datatypes like "Money" or "PhoneNbr"
In addition to these features, both have a few extra ones. Postgresql has things like inheritance and user definable index methods. Firebird has distributed two-phase commit where you can have a transaction across multiple servers. You can use Firebird as a local database file (like Access) to create desktop applications. There are other nuances, so these are just a couple of examples. Look at the following hyperlinks for more detail

http://www.mysql.com/doc/en/Features.html
http://www.postgresql.org/users-lounge/features.html
http://firebird.sourceforge.net/index.php?op=guide&id=ib6_overview
http://firebird.sourceforge.net/index.php?op=guide&id=ib6_newfeatures
http://firebird.sourceforge.net/index.php?op=guide&id=ib6_techspec


What is the criteria to select one amongst three of them?I would choose either Firebird or Postgresql for any of the work I do. Both are great databases. I would favor them over MySQL because they can do everything MySQL does and more. They simply have more robust features. I chose Firebird because I found it easy to setup, administer and it was a nice fit with my Borland tools. Because Firebird 1.0 was based on Interbase 6.0, if I was going to try to convince someone to consider using FB, I would tell them to look at documents like this that emphasize the features of Interbase (this document is for the current version of 7.0 so some of the newest features may not be in Firebird 1.0. I haven't tested Firebird 1.5 yet, so I don't know all its new features):
http://www.borland.com/interbase/pdf/ib7_feaben.pdf

Thanks a lot for detail explanation.

I think I should fully test Firebird SQL and PostgreSQL as soon as possible so that I could persue my boss to change from MySQL.

One more hyperlink for you...

If I wanted to convince someone to use Postgresql, I would send them to http://advocacy.postgresql.org/ and tell them to look at the Advantages and Case Studies hyperlinks.

One point that wasn't mentioned so far is, that php's support for mysql is more complete and sometimes more stable than the support for Firebird/Interbase.

And I wouldn't agree that mysql compares to Firebird like a Hyundai to a BMW. It is more like a hammer and a srewdriver. One have to choose the right tool for a specific task.

Lutz

Originally posted by irie
...php's support for mysql is more complete...And Interbase/Firebird has better support for data aware controls for developing fat clients which is the type of development I do. For me, the triggers, stored procedures, etc. are much more important than PHP support. For some developers, PHP support might be the ONLY important criteria.

And I wouldn't agree that mysql compares to Firebird like a Hyundai to a BMW.That was a mistype. I meant to say a Daewoo.;) The main reason I prefer Firebird over MySQL is that I can do everything with Firebird that I can do with MySQL, but not vice versa. I used MySQL for years and got really good use out it. I can understand why people like it. But Postgresql and Firebird are more full-featured databases. I can do ALL my database development with Firebird while MySQL always leaves me wishing for more.

It seems in many aspects MySQL V. Firebird is really a matter of preference and application.

Just to add another angle to the discussion, is anyone aware of any major corporations using Firebird as their core RDBMS? We all hear people rhapsodising about MySQL being used at Google and Yahoo - and I have to admit, that these comments improve my confidence in the product. What similar accolades does Firebird have to it's name? (or even Postgres for that matter)


christo










privacy (GDPR)