[Asterisk-Users] OT: Best DB

Linterra linterra at gmail.com
Wed Mar 16 16:11:38 MST 2005


My apologies to the rest of the readers for the flame, but Mr
Salvatore, you are sadly misinformed.

I like MySQL as well as PostgreSQL and they both have their merits,
but it's annoying to see someone give a recommendation of one over the
other based on ignorance instead of relevant facts.

If you're developing an application which you would eventually like to
redistribute and you want to be able to redistribute the database
without any licensing fees, the BSD license will allow you to do so,
but the MySQL license does not.  Anyone prefering the GPL license over
the BSD, MPL or Apache licenses is either ignorant, stupid,
financially independent or a GPL zealot.

As far as technical support goes, several large reliable companies
provide paid support for PostgreSQL if you feel the need to pay for
support.  For example, Pervasive (absolutely not a "mom and pop shop")
provides paid technical support for PostgreSQL.  I've used their
products and services for many years and they are extremely reliable,
responsive and not too expensive.  But, you can get just as good
support directly from the developers making paid support irrelevant
until you hit upon a question which cannot be solved by the developers
(unlikely but possible).  Command Prompt, Inc. also provides 24/7/365
support and there are many others listed on the PostgreSQL web site.

MySQL and PostgreSQL will both probably get the job done.  MySQL is
slightly faster under simpler conditions.  PostgreSQL will outperform
MySQL on larger more complicated queries.

For example, on the same computer running both databases, the
following query took over six minutes to complete on MySQL and less
than three seconds on PostgreSQL.  Both databases had the same indexes
defined and contained exactly the same data.

select *
from  Listing, InHouseListing, Trans, TransactionDetail, DepositItem,
DepositDetail, chart
where Listing.listingId = InHouseListing.listingId
and InHouseListing.listingTransId = Trans.transId
and InHouseListing.closed = 1
and TransactionDetail.transId = Trans.transId
and TransactionDetail.transactionItemType = 6 
and TransactionDetail.transactionCategoryId = 1
and DepositItem.listingId = Listing.listingId
and DepositItem.depositItemId = DepositDetail.depositItemId
and DepositDetail.debit = TransactionDetail.actualAmount 
and chart.id = DepositDetail.chartId

Also, the following query on MySQL (not my example but another one
that I researched)

SELECT DISTINCT d.ID, sqt.policy_reference, sr.*
FROM d, dsr, sr, sqt
WHERE (d.ID = dsr.dbramm_id) AND (dsr.scs_risk_list_id = sr.ID) AND
(sr.scs_base_risk_question_number = sqt.qindex)
AND (d.ID IN(SELECT DISTINCT d2.ID FROM DBRAMM AS d2 WHERE
d2.real_dbramm_id = 216)))
----------------------------------------------------------------------
[requires minutes]

compared to the identical query without subselect:

----------------------------------------------------------------------
SELECT DISTINCT d.ID, sqt.policy_reference, sr.*
FROM d, dsr, sr, sqt
WHERE (d.ID = dsr.dbramm_id) AND (dsr.scs_risk_list_id = sr.ID) AND
(sr.scs_base_risk_question_number = sqt.qindex)
AND (d.ID IN(1,34,15,36,324,...some more))
----------------------------------------------------------------------
[requires 8 seconds]

Whereas the same data on PostgreSQL took < 6 seconds in both cases.

Also, if you need triggers written in more than one scripting
language, custom data types, custom indexes, references to external
data and many many more features, you won't find them in MySQL.

Simple is better until you need complex, so my bottom line
recommendation is this:  Write your code in a database independent way
and use MySQL until you outgrow it (which may never happen), then
switch to PostgreSQL.



More information about the asterisk-users mailing list