Re: [PHP] Survey: MySQL vs PostgreSQL for PHP
I've been using MySQL/PHP for quite some time. Several months ago, I wanted to port a project over to PostgreSQL. I found everything about pg (eg the website, documentation, installation process) far less straight ahead than MySQL. So much so, that I didn't get around to actually installing pg. I actually found PostgreSQL more straight-forward, since the arguments are required. (Or used to be.) So I better understood what was going on in PostgreSQL. Not as many examples out there but, really, changing: mysql_query($query) to pg_exec($connection, $query) in reading the examples is not that tricky... Oh well. Different tastes for different folks. Plus, as others have pointed out, the supporting functions in PHP aren't as powerful/diverse. For example, there's no insert id function. Yes, there *IS* such a function, but it's a two-step process. $oid = pg_getlastoid() followed by a SELECT ... where oid = $oid http://www.php.net/manual/en/function.pg-last-oid.php OID stands for Object ID, and every PosgreSQL object has a unique OID. Every row, every Large Object, every everything. Whoops. Just double-checked, and apparently pSQL 7.2 doesn't *have* to have OIDs for everything. This could get interesting, folks... A quick perusal of the PHP Docs makes me say: Only get rid of the optional OID if you are 100% sure you'll never need go use pg_last_oid() or you'll be in deep trouble. You do *NOT* want to just use the oid as my own id however. OIDs can change when you export/import database data, if you're not careful, and it's just bad form to use that internal ID as your own. PostgreSQL (and any SQL database) would be completely un-usable (*) if there wasn't some way to reliably get the last tuple inserted from your own connection. Kinda funny that never made it into SQL92 :-) (*) Yeah, okay, you can generate your own unique ID before the insert, and then SELECT on that to get the auto-generated ID... Might as well not bother with an auto-generated ID, then, eh? -- Like Music? http://l-i-e.com/artists.htm -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Survey: MySQL vs PostgreSQL for PHP
Thanks to everyone who contributed on this thread. It proved to be very informative and enjoyable. -Ed This message is intended for the sole use of the individual and entity to whom it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the intended addressee, nor authorized to receive for the intended addressee, you are hereby notified that you may not use, copy, disclose or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email and delete the message. Thank you very much. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Survey: MySQL vs PostgreSQL for PHP
From the php.net manual for pg_fetch_array: Note: From 4.1.0, row became optional. Calling pg_fetch_array() will increment internal row counter by 1. Ilia A. wrote: On July 5, 2002 07:54 pm, Pete James wrote: Ilia A. wrote: The biggest annoyance I've come across is the fact that while using PostgreSQL with PHP is that when you fetch a row you must specify the number of the result, while in MySQL, that is handled internally by PHP for you. This means that your PHP scripts must track the row numbers themselves. This is not so... see pg_fetch_array. Since PHP 4.1.0, you no longer need the row number. It may work without, but according to the manual on php.net pg_fetch_array pg_fetch_object pg_fetch_row REQUIRE a row number. If that is no longer the case as you claim, perphaps someone needs to inform the developers and have them update the documentation. Now we come to the actual database speed itself. In this regard in most applications MySQL is MUCH faster probably because it has to do allot less work then PostgreSQL does. For example, lets analyze the most common action performed in a database system, a SELECT. When you do a select in MySQL, MySQL internally locks the table for the duration of the select. PostgreSQL on the other hand does a row level lock, internally, for every row you select. Is this really what you want? Doesn't this mean that PostgreSQL would be more efficient for larger user volumes? Locking an entire table isn't usually a good thing. Not necessarily, locking entire table has its pluses and minuses. The BIG minues is that while the entire table is locked you cannot do anything until the lock is released. On the other hand, it is MUCH faster to lock the entire table then the inidividual rows. PostgreSQL would be more effecient on a system that does lots of locking, but on a system without or few locks MySQL will beat it hands down. Ilia FUDforum Core Developer [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Survey: MySQL vs PostgreSQL for PHP
On Fri, Jul 05, 2002 at 01:59:48PM -0700, Lazor, Ed wrote: How many here feel PostgreSQL has surpassed MySQL as the better backend for PHP? This would be based on performance (speed, scalability, etc.) and features. I've been using MySQL/PHP for quite some time. Several months ago, I wanted to port a project over to PostgreSQL. I found everything about pg (eg the website, documentation, installation process) far less straight ahead than MySQL. So much so, that I didn't get around to actually installing pg. Plus, as others have pointed out, the supporting functions in PHP aren't as powerful/diverse. For example, there's no insert id function. --Dan -- PHP classes that make web design easier SQL Solution | Layout Solution | Form Solution sqlsolution.info | layoutsolution.info | formsolution.info T H E A N A L Y S I S A N D S O L U T I O N S C O M P A N Y 4015 7 Av #4AJ, Brooklyn NY v: 718-854-0335 f: 718-854-0409 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Survey: MySQL vs PostgreSQL for PHP
On July 5, 2002 07:54 pm, Pete James wrote: Ilia A. wrote: The biggest annoyance I've come across is the fact that while using PostgreSQL with PHP is that when you fetch a row you must specify the number of the result, while in MySQL, that is handled internally by PHP for you. This means that your PHP scripts must track the row numbers themselves. This is not so... see pg_fetch_array. Since PHP 4.1.0, you no longer need the row number. It may work without, but according to the manual on php.net pg_fetch_array pg_fetch_object pg_fetch_row REQUIRE a row number. If that is no longer the case as you claim, perphaps someone needs to inform the developers and have them update the documentation. Now we come to the actual database speed itself. In this regard in most applications MySQL is MUCH faster probably because it has to do allot less work then PostgreSQL does. For example, lets analyze the most common action performed in a database system, a SELECT. When you do a select in MySQL, MySQL internally locks the table for the duration of the select. PostgreSQL on the other hand does a row level lock, internally, for every row you select. Is this really what you want? Doesn't this mean that PostgreSQL would be more efficient for larger user volumes? Locking an entire table isn't usually a good thing. Not necessarily, locking entire table has its pluses and minuses. The BIG minues is that while the entire table is locked you cannot do anything until the lock is released. On the other hand, it is MUCH faster to lock the entire table then the inidividual rows. PostgreSQL would be more effecient on a system that does lots of locking, but on a system without or few locks MySQL will beat it hands down. Ilia FUDforum Core Developer [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Survey: MySQL vs PostgreSQL for PHP
Hi, On Fri, 5 Jul 2002, Lazor, Ed wrote: How many here feel PostgreSQL has surpassed MySQL as the better backend for PHP? This would be based on performance (speed, scalability, etc.) and features. Surely PostgreSQL!!! If you aim is creating a database, then use PostgreSQL. if you want to use a database, for ex. for you web page, use MySQL... (well, use PostgreSQL anyway :-) ) As written on postgresql.org : The best open source database engine BEst regards. -- Devrim GUNDUZ [EMAIL PROTECTED] [EMAIL PROTECTED] Web : http://devrim.oper.metu.edu.tr - -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Survey: MySQL vs PostgreSQL for PHP
I am there with you on PostgreSQL. B i g D o g - Original Message - From: Lazor, Ed [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, July 05, 2002 2:59 PM Subject: [PHP] Survey: MySQL vs PostgreSQL for PHP How many here feel PostgreSQL has surpassed MySQL as the better backend for PHP? This would be based on performance (speed, scalability, etc.) and features. -Ed This message is intended for the sole use of the individual and entity to whom it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the intended addressee, nor authorized to receive for the intended addressee, you are hereby notified that you may not use, copy, disclose or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email and delete the message. Thank you very much. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Survey: MySQL vs PostgreSQL for PHP
I dunno! Never tried it! I wonder which one is easier to work with? B I G D O G [EMAIL PROTECTED] wrote in message 00d201c22467$8c2e4830$[EMAIL PROTECTED]">news:00d201c22467$8c2e4830$[EMAIL PROTECTED]... I am there with you on PostgreSQL. B i g D o g - Original Message - From: Lazor, Ed [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, July 05, 2002 2:59 PM Subject: [PHP] Survey: MySQL vs PostgreSQL for PHP How many here feel PostgreSQL has surpassed MySQL as the better backend for PHP? This would be based on performance (speed, scalability, etc.) and features. -Ed This message is intended for the sole use of the individual and entity to whom it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the intended addressee, nor authorized to receive for the intended addressee, you are hereby notified that you may not use, copy, disclose or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email and delete the message. Thank you very much. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Survey: MySQL vs PostgreSQL for PHP
I've always used MySQL, I don't know either. I suspect they are equal in ease of use. -Original Message- I dunno! Never tried it! I wonder which one is easier to work with? This message is intended for the sole use of the individual and entity to whom it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the intended addressee, nor authorized to receive for the intended addressee, you are hereby notified that you may not use, copy, disclose or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email and delete the message. Thank you very much. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Survey: MySQL vs PostgreSQL for PHP
I wrote a small article for builder.com discussing MySQL vs PostgreSQL. There is a great dicussion happening on that article. Many are voicing their opinions for mysql. Here is the url: http://builder.com.com/article.jhtml?id=u00320020624gcn01.htm In short PostgreSQL offers many advanced features like triggers and views. Check it out and see what you think... B i g D o g - Original Message - From: Lazor, Ed [EMAIL PROTECTED] To: 'Scott Fletcher' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, July 05, 2002 3:07 PM Subject: RE: [PHP] Survey: MySQL vs PostgreSQL for PHP I've always used MySQL, I don't know either. I suspect they are equal in ease of use. -Original Message- I dunno! Never tried it! I wonder which one is easier to work with? This message is intended for the sole use of the individual and entity to whom it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the intended addressee, nor authorized to receive for the intended addressee, you are hereby notified that you may not use, copy, disclose or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email and delete the message. Thank you very much. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Survey: MySQL vs PostgreSQL for PHP
I don't feel that. I run a web site with a big deal of traffic, and in speed and scalability, MySQL is better than PGSQL or Sybase. I know because I tryed each one of these before getting to MySQL in definitive. Sybase is WAY too slow, and Postgres has a habit of melting down from time to time. In the features field, if you need referential integrity you can use InnoDB or BDB tables in MySQL, wich supports ON DELETE CASCADE and many things more, like row-level locking, blah, blah, blah. To keep your data secure, Sybase is a better option than PGSQL, because when PGSQL melts down, you lose some records as a gift. I had not seen this behavior mainly because I did not ran PGSQL long enough to see this, and the memory requirements of Sybase are just too much to stand. I prefer MySQL above every other, even lacking SP's. Of course, my business do not require anything more than standard MyISAM offers, so PGSQL may be a better option in some cases. On Friday 05 July 2002 17:59, Lazor, Ed wrote: How many here feel PostgreSQL has surpassed MySQL as the better backend for PHP? This would be based on performance (speed, scalability, etc.) and features. -Ed -- João Paulo Vasconcellos Gerente de Tecnologia - NetCard Tel. 21 3852-9008 Ramal 31 [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Survey: MySQL vs PostgreSQL for PHP
That's a really good articles!!! Ray Hunter [EMAIL PROTECTED] wrote in message 00f201c22469$99c78130$[EMAIL PROTECTED]">news:00f201c22469$99c78130$[EMAIL PROTECTED]... I wrote a small article for builder.com discussing MySQL vs PostgreSQL. There is a great dicussion happening on that article. Many are voicing their opinions for mysql. Here is the url: http://builder.com.com/article.jhtml?id=u00320020624gcn01.htm In short PostgreSQL offers many advanced features like triggers and views. Check it out and see what you think... B i g D o g - Original Message - From: Lazor, Ed [EMAIL PROTECTED] To: 'Scott Fletcher' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, July 05, 2002 3:07 PM Subject: RE: [PHP] Survey: MySQL vs PostgreSQL for PHP I've always used MySQL, I don't know either. I suspect they are equal in ease of use. -Original Message- I dunno! Never tried it! I wonder which one is easier to work with? This message is intended for the sole use of the individual and entity to whom it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the intended addressee, nor authorized to receive for the intended addressee, you are hereby notified that you may not use, copy, disclose or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email and delete the message. Thank you very much. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Survey: MySQL vs PostgreSQL for PHP
I've recently had the 'pleasure' of porting a large MySQL application to support PostgreSQL, through that experience I've gained some insight into the differences between the two as well as how they are supported by PHP. First allow me to point out that MySQL support in PHP is a lot more mature then PostgreSQL support. This can be seen through several factors, such as larger number of functions, which do more as well as the fact that only recently (php 4.2.0) did PostgreSQL interface functions got renamed to a more standard convention that MySQL functions already used since 3.0 days. For example, while using MySQL you can easily get properties of a field by simply using the mysql_field_type() function, pg_* has nothing similar. The only alternative is to run a query on the PostgreSQL internal tables, which contain the properties of a column. The biggest annoyance I've come across is the fact that while using PostgreSQL with PHP is that when you fetch a row you must specify the number of the result, while in MySQL, that is handled internally by PHP for you. This means that your PHP scripts must track the row numbers themselves. PostgreSQL also has a number of things that it lacks when compared to MySQL. For example, it does not support unsigned integers, which forces you to use int8 to store timestamps, which will eventually reach 4.2 billion. In case you are wondering what is the big deal, int4 or int8, besides 4 more bytes of memory, well on x86 processors, which are 32 bit, 32 bit numbers are handled faster then their 64 bit counterparts. Now a days the performance drop as the result is quite small, but in the end everything adds up. PostgreSQL also does not support ENUM type, although you can port your ENUMs to PostgreSQL by using CONSTANTS. Now we come to the actual database speed itself. In this regard in most applications MySQL is MUCH faster probably because it has to do allot less work then PostgreSQL does. For example, lets analyze the most common action performed in a database system, a SELECT. When you do a select in MySQL, MySQL internally locks the table for the duration of the select. PostgreSQL on the other hand does a row level lock, internally, for every row you select. PostgreSQL also does not optimize count(),max() and min() queries, which in MySQL are instant regardless of the table size since they are cached internally. PostgreSQL on the other hand needs to go every single row in the table. However, I should note that PostgreSQL developers I spoke to, told me that they plan to add this kind of functionality in the next release of PostgreSQL, whenever that happens. PostgreSQL also syncs any inserted or updated to disk right away to ensure that you don't loose any data should the computer crash on the other hand MySQL keeps memory buffers and will often not sync to disk right away to avoid disk IO. PostgreSQL offers greater data security, which would be important in a shopping cart, however it looses much speed in this approach compared to MySQL's approach which is ideal for programs where fast inserts are critical, like a web counter for example. PostgreSQL does have a number of advantages that should not be overlooked and are certainly very important. Such as views, that allow you to create 'virtual tables' who's data is a combination of a complex join, which simplifies your selects to select * from table1_table2_view; PostgreSQL supports triggers and stored procedures which can be coded in perl, python, plsql and C. These triggers and functions can clean up not to mention speed up you code by moving various database code from PHP to PostgreSQL. PostgreSQL also supports various table locking implementations, which include row level locking. The benefit of using it is what while a certain row in a table is locked other users who do not require this row can still read from a table. In MySQL once a table is locked to write, no other user can read from the very same table until the lock is released. This in particular makes PostgreSQL much more scalable then MySQL. Unlike MySQL, PostgreSQL supports transactions with rollbacks, which allow you to actually make a bunch of queries and then with a single commit apply them to the database. The cool thing is that you can actually undo bad query by canceling the transaction. Bottom line is that both MySQL and PostgreSQL have their 'markets'. IMHO in most cases MySQL is a simpler, faster and easier solution to use. However, if you require 100% data integrity and are dealing with sensitive data and in those case probably can spend a little more or hardware PostgreSQL should be your tool of choice. Ilia FUDforum Core Developer [EMAIL PROTECTED] On July 5, 2002 04:59 pm, Lazor, Ed wrote: How many here feel PostgreSQL has surpassed MySQL as the better backend for PHP? This would be based on performance (speed, scalability, etc.) and features. -Ed
Re: [PHP] Survey: MySQL vs PostgreSQL for PHP
ðÒÉ×ÅÔ!
That's interesting. I actually never used Postgres on production
environments, so... How often does it melt? And is there a known reason
or it's just a matter of *luck*?
My opinion is that Mysql is after all nothing more than an ISAM file
system, which can be queried by SQL. And it's more than enough for most
small sized projects.
I dunno about InnoDB and BDB, but once referential integrity is the
issue I'd move straight to Oracle and avoid the hassle of debugging some
new engine features myself. At least, in a production environment.
Oracle will also perform much better than anything else, if properly
configured and maintained. But moving to such an environment requires a
full time administrator if you do not want your performance to downgrade
dramatically after some time.
In a web environment usually people do not want to spend that much
money, so actually most of the stable stuff will probably remain on the
ISAM file systems for a while more. After all it works, right?
ÐÏËÁ
áÌØÂÅÒÔÏ
ëÉÅ×
Joa~o Paulo Vasconcellos wrote:
I don't feel that. I run a web site with a big deal of traffic, and in speed
and scalability, MySQL is better than PGSQL or Sybase. I know because I tryed
each one of these before getting to MySQL in definitive. Sybase is WAY
too slow, and Postgres has a habit of melting down from time to time. In the
features field, if you need referential integrity you can use InnoDB or BDB
tables in MySQL, wich supports ON DELETE CASCADE and many things more, like
row-level locking, blah, blah, blah. To keep your data secure, Sybase is a
better option than PGSQL, because when PGSQL melts down, you lose some
records as a gift. I had not seen this behavior mainly because I did not ran
PGSQL long enough to see this, and the memory requirements of Sybase are just
too much to stand. I prefer MySQL above every other, even lacking SP's. Of
course, my business do not require anything more than standard MyISAM offers,
so PGSQL may be a better option in some cases.
On Friday 05 July 2002 17:59, Lazor, Ed wrote:
How many here feel PostgreSQL has surpassed MySQL as the better backend for
PHP? This would be based on performance (speed, scalability, etc.) and
features.
-Ed
--
-_=}{=_-@-_=}{=_--_=}{=_-@-_=}{=_--_=}{=_-@-_=}{=_--_=}{=_-
LoRd, CaN yOu HeAr Me, LiKe I'm HeArInG yOu?
lOrD i'M sHiNiNg...
YoU kNoW I AlMoSt LoSt My MiNd, BuT nOw I'm HoMe AnD fReE
tHe TeSt, YeS iT iS
ThE tEsT, yEs It Is
tHe TeSt, YeS iT iS
ThE tEsT, yEs It Is...
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
