Re: PgSQL vs MySQL
Jeremy Smith wrote: I have an extrememly query heavy site that I tried to switch from MySQL to PgSQL. And after spending literally a week reconfiguring thousands of queries and rewriting code, I finally had the pgSQL version of the site live, but when I had even a trickle of users on the site it was HORRENDOUSLY slow. Now, if I had optimized my code and my queries for another 3 weeks, I may have had something that was usable. But I will take the speed of MySQL and live without some of the niceties of PgSQL. Jeremy -Original Message- From: David Griffiths [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 03, 2004 1:28 PM To: [EMAIL PROTECTED]; Mark Warner; [EMAIL PROTECTED] Subject: Re: PgSQL vs MySQL As a DBA and someone who has worked both with PostgresQL and MySQL, I think I can answer this knowingly. First, MySQL is significantly faster than PostgresQL and Oracle. Second, MySQL is also a simpler database to set up and configure. Third, the documentation is better, and there are far more third party books out there. Fourth, MySQL has a more impressive list of customers. Yes, there are some large PostgresQL customers (the .org domain system?), but none like Yahoo and Slashdot. MySQL does not have triggers, stored procedures or views yet. Sub-selects should be out in six months. After fighting with PostgresQL to try to get it to use indexes, rewriting tonnes of queries, and still getting poor performance, I gave up on it. I prefer MySQL with InnoDB. Well here is a case where some guy tried very hard to convert his app from MySQL to PostgreSQL and the result was that he couldn't get MySQL be as fast as PostgreSQL. http://archives.postgresql.org/pgsql-general/2003-11/msg01399.php http://archives.postgresql.org/pgsql-general/2003-11/msg01458.php Kaarel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
PgSQL vs MySQL
What advantages, besides ease of setup, does MySQL hold over PostgreSQL? It would seem, to me, that the two are close competitors (both in quality, and performance). Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PgSQL vs MySQL
What advantages, besides ease of setup, does MySQL hold over PostgreSQL? It would seem, to me, that the two are close competitors (both in quality, and performance). Are you sure about quality? Check out: http://sql-info.de/mysql/gotchas.html You can check out postgresql's on the same site but they are significally less critical. After reviewing this and talking to some other people i'm switching over to postgresql. I'm sure there is a place with mysql but I don't think i'd trust it for anything critical unless you very confident your developers know what they are doing. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PgSQL vs MySQL
David wrote: Are you sure about quality? Check out: http://sql-info.de/mysql/gotchas.html Those have nothing to do with the quality of MySQL. I don't think i'd trust it for anything critical unless you very confident your developers know what they are doing. Would you trust anything to developers who do not know what they are doing? As a _novice_ developer I learned the noted aspects of MySQL: it's part of knowing how the database works and how to use it, and not terribly sophisticated. I think if a database developer would not get into that level of detail, you are going to have problems no matter what database they use. That said, postgresql may be a fine choice. Enjoy. Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PgSQL vs MySQL
Are you sure about quality? Check out: http://sql-info.de/mysql/gotchas.html Those have nothing to do with the quality of MySQL. No? Why not? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: PgSQL vs MySQL
Would you trust anything to developers who do not know what they are doing? As a _novice_ developer I learned the noted aspects of MySQL: it's part of knowing how the database works and how to use it, and not terribly sophisticated. Then you may be a good developer but experience tells me (7 years as an Oracle DBA) that developers don't take the time to learn the intricacies of a particular database. So if you plan on using mysql, make all your developers read that gotcha's page. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PgSQL vs MySQL
The thing which bothers me most about MySQL is the lack of a proper boolean. I don't like having to abstract a tinyint(1) into true or false. As much of my work involves building applications with Yes or No questions, I think I am switching to PostgreSQL. [EMAIL PROTECTED] wrote: What advantages, besides ease of setup, does MySQL hold over PostgreSQL? It would seem, to me, that the two are close competitors (both in quality, and performance). Are you sure about quality? Check out: http://sql-info.de/mysql/gotchas.html You can check out postgresql's on the same site but they are significally less critical. After reviewing this and talking to some other people i'm switching over to postgresql. I'm sure there is a place with mysql but I don't think i'd trust it for anything critical unless you very confident your developers know what they are doing. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PgSQL vs MySQL
In the last episode (Mar 03), [EMAIL PROTECTED] said: Would you trust anything to developers who do not know what they are doing? As a _novice_ developer I learned the noted aspects of MySQL: it's part of knowing how the database works and how to use it, and not terribly sophisticated. Then you may be a good developer but experience tells me (7 years as an Oracle DBA) that developers don't take the time to learn the intricacies of a particular database. So if you plan on using mysql, make all your developers read that gotcha's page. You may need better developers then :) If you don't learn about the database you're coding for, you are always going to get average or below-average performance/quality. All databases have their gotchas. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PgSQL vs MySQL
:-)
someflag enum('TRUE','FALSE');
Not quite boolean, but it works.
Curtis
On Wed, 3 Mar 2004, Mark Warner wrote:
The thing which bothers me most about MySQL is the lack of a proper
boolean. I don't like having to abstract a tinyint(1) into true or
false. As much of my work involves building applications with Yes or No
questions, I think I am switching to PostgreSQL.
[EMAIL PROTECTED] wrote:
What advantages, besides ease of setup, does MySQL hold over PostgreSQL?
It would seem, to me, that the two are close competitors (both in
quality, and performance).
Are you sure about quality? Check out:
http://sql-info.de/mysql/gotchas.html
You can check out postgresql's on the same site but they are significally less
critical. After reviewing this and talking to some other people i'm switching
over to postgresql. I'm sure there is a place with mysql but I don't think i'd
trust it for anything critical unless you very confident your developers know what
they are doing.
--
--
Curtis Maurand
mailto:[EMAIL PROTECTED]
http://www.maurand.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PgSQL vs MySQL
As a DBA and someone who has worked both with PostgresQL and MySQL, I think I can answer this knowingly. First, MySQL is significantly faster than PostgresQL and Oracle. Second, MySQL is also a simpler database to set up and configure. Third, the documentation is better, and there are far more third party books out there. Fourth, MySQL has a more impressive list of customers. Yes, there are some large PostgresQL customers (the .org domain system?), but none like Yahoo and Slashdot. MySQL does not have triggers, stored procedures or views yet. Sub-selects should be out in six months. After fighting with PostgresQL to try to get it to use indexes, rewriting tonnes of queries, and still getting poor performance, I gave up on it. I prefer MySQL with InnoDB. Some of the gotcha's are valid, and others can be found in any database. Forewarned is forearmed. David. - Original Message - From: [EMAIL PROTECTED] To: Mark Warner [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, March 03, 2004 6:51 AM Subject: Re: PgSQL vs MySQL What advantages, besides ease of setup, does MySQL hold over PostgreSQL? It would seem, to me, that the two are close competitors (both in quality, and performance). Are you sure about quality? Check out: http://sql-info.de/mysql/gotchas.html You can check out postgresql's on the same site but they are significally less critical. After reviewing this and talking to some other people i'm switching over to postgresql. I'm sure there is a place with mysql but I don't think i'd trust it for anything critical unless you very confident your developers know what they are doing. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PgSQL vs MySQL
from the manual it appears that char(0) null default null can be
used as a boolean, will the values of either null or . haven't
tried it myself, but its documented.
http://www.mysql.com/documentation/mysql/bychapter/manual_Column_types.html#Column_types
[NATIONAL] CHAR(M) [BINARY | ASCII | UNICODE]
This is also quite nice when you need a column that only can
take 2 values: A CHAR(0), that is not defined as NOT NULL, will only
occupy one bit and can only take 2 values: NULL or . See section
6.2.3.1 The CHAR and VARCHAR Types.
On Wednesday 03 March 2004 11:43, Curtis Maurand wrote:
:-)
someflag enum('TRUE','FALSE');
Not quite boolean, but it works.
Curtis
On Wed, 3 Mar 2004, Mark Warner wrote:
The thing which bothers me most about MySQL is the lack of a
proper boolean. I don't like having to abstract a tinyint(1) into
true or false. As much of my work involves building applications
with Yes or No questions, I think I am switching to PostgreSQL.
[EMAIL PROTECTED] wrote:
What advantages, besides ease of setup, does MySQL hold over
PostgreSQL? It would seem, to me, that the two are close
competitors (both in quality, and performance).
Are you sure about quality? Check out:
http://sql-info.de/mysql/gotchas.html
You can check out postgresql's on the same site but they are
significally less critical. After reviewing this and talking
to some other people i'm switching over to postgresql. I'm
sure there is a place with mysql but I don't think i'd trust it
for anything critical unless you very confident your developers
know what they are doing.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PgSQL vs MySQL
One other point that I forgot I mentioned - PostgresQL does not have a decent replication solution. There are a few solutions, including one from PostgreSQL.com. The PostgresQL.com version is not the latest - you need to pay for support to get that. The other ones were (last I looked) incomplete. Davi. - Original Message - From: David Griffiths [EMAIL PROTECTED] To: [EMAIL PROTECTED]; Mark Warner [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, March 03, 2004 10:27 AM Subject: Re: PgSQL vs MySQL As a DBA and someone who has worked both with PostgresQL and MySQL, I think I can answer this knowingly. First, MySQL is significantly faster than PostgresQL and Oracle. Second, MySQL is also a simpler database to set up and configure. Third, the documentation is better, and there are far more third party books out there. Fourth, MySQL has a more impressive list of customers. Yes, there are some large PostgresQL customers (the .org domain system?), but none like Yahoo and Slashdot. MySQL does not have triggers, stored procedures or views yet. Sub-selects should be out in six months. After fighting with PostgresQL to try to get it to use indexes, rewriting tonnes of queries, and still getting poor performance, I gave up on it. I prefer MySQL with InnoDB. Some of the gotcha's are valid, and others can be found in any database. Forewarned is forearmed. David. - Original Message - From: [EMAIL PROTECTED] To: Mark Warner [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, March 03, 2004 6:51 AM Subject: Re: PgSQL vs MySQL What advantages, besides ease of setup, does MySQL hold over PostgreSQL? It would seem, to me, that the two are close competitors (both in quality, and performance). Are you sure about quality? Check out: http://sql-info.de/mysql/gotchas.html You can check out postgresql's on the same site but they are significally less critical. After reviewing this and talking to some other people i'm switching over to postgresql. I'm sure there is a place with mysql but I don't think i'd trust it for anything critical unless you very confident your developers know what they are doing. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: PgSQL vs MySQL
As a DBA I have a few questions about what you said here. You have worked with both PostgreSQL and MySQL, and yet you say that MySQL is 'signifigantly' faster than Oracle? Can you PROVE that? And how is it faster to chase down data problems when MySQL has no native constraints in it data design? Wouldn't you think that since MySQL is 'simpler' to set up and configure that it just lends itself to poor design principles and that you will constantly be fighting with it after a point? Or migrating to something else? Can't argue with the third point at all. In all of this, isn't it really InnoDB that you like, and not MySQL specifically? -James -Original Message- From: David Griffiths [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 03, 2004 10:28 AM To: [EMAIL PROTECTED]; Mark Warner; [EMAIL PROTECTED] Subject: Re: PgSQL vs MySQL As a DBA and someone who has worked both with PostgresQL and MySQL, I think I can answer this knowingly. First, MySQL is significantly faster than PostgresQL and Oracle. Second, MySQL is also a simpler database to set up and configure. Third, the documentation is better, and there are far more third party books out there. Fourth, MySQL has a more impressive list of customers. Yes, there are some large PostgresQL customers (the .org domain system?), but none like Yahoo and Slashdot. MySQL does not have triggers, stored procedures or views yet. Sub-selects should be out in six months. After fighting with PostgresQL to try to get it to use indexes, rewriting tonnes of queries, and still getting poor performance, I gave up on it. I prefer MySQL with InnoDB. Some of the gotcha's are valid, and others can be found in any database. Forewarned is forearmed. David. - Original Message - From: [EMAIL PROTECTED] To: Mark Warner [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, March 03, 2004 6:51 AM Subject: Re: PgSQL vs MySQL What advantages, besides ease of setup, does MySQL hold over PostgreSQL? It would seem, to me, that the two are close competitors (both in quality, and performance). Are you sure about quality? Check out: http://sql-info.de/mysql/gotchas.html You can check out postgresql's on the same site but they are significally less critical. After reviewing this and talking to some other people i'm switching over to postgresql. I'm sure there is a place with mysql but I don't think i'd trust it for anything critical unless you very confident your developers know what they are doing. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: PgSQL vs MySQL
I do not approve or disapprove of your choice of Postgres over MySQL. I don't profess to be a postgresql expert, i'm still learning about it.. but a quick glance at the documentation tells me that the only option would be to use pg_dump utility which creates a sql file. I haven't looked at one of these files but I guess to do a point in time recovery you would delete all of the sql after that PIT. You could also possibly write a script to backup the data in individual tables by locking the table and then 'selecting' out the data like in mysql. Like I said, i'm not an expert in either mysql or postgresql and each dbms has its place.. Its just I would probably use postgresql for a more critical database. Actually, if the database is critical i'd be using oracle. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PgSQL vs MySQL
Hm. I am not a professional DBA, but I have used both MySQL and PostreSQL for the same application, and I can say that I am more satisfied with PgSQL. It is faster and more reliable, on the platform that I have built (Dual Opteron 244 w/ 3GB of PC3200). While I had few technical problems with MySQL, I just never felt comfortable using it, so I have switched to PgSQL for everything. Serials (aka auto-increment in MySQL) work fantastically. The ability to SET the value of the serial is useful. I tried for weeks to replicate that functionaility with MySQL, and failed. The core superiority of the PgSQL SERIAL, as opposed to the MySQL AUTO-INCREMENT lies in the fact that the serials' values are stored in a table, and can be set, read, and predicted accurately, and easily. My database is somewhat small (6-8GB), and PgSQL offered an almost 15% performance increase over MySQL, for the same data. Additionally, my support code was simplified drastically by some of the core functions of PgSQL. For this reason, I have abandoned MySQL, and switched to PgSQL. Also, in the past week, there have been about 10 times as many technical support problems in this mailing list, as in the relative PgSQL list. This supports my belief that PgSQL is a better DB platform. Thank you all for your information. Good Day, Mark Warner. David Griffiths wrote: One other point that I forgot I mentioned - PostgresQL does not have a decent replication solution. There are a few solutions, including one from PostgreSQL.com. The PostgresQL.com version is not the latest - you need to pay for support to get that. The other ones were (last I looked) incomplete. Davi. - Original Message - From: David Griffiths [EMAIL PROTECTED] To: [EMAIL PROTECTED]; Mark Warner [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, March 03, 2004 10:27 AM Subject: Re: PgSQL vs MySQL As a DBA and someone who has worked both with PostgresQL and MySQL, I think I can answer this knowingly. First, MySQL is significantly faster than PostgresQL and Oracle. Second, MySQL is also a simpler database to set up and configure. Third, the documentation is better, and there are far more third party books out there. Fourth, MySQL has a more impressive list of customers. Yes, there are some large PostgresQL customers (the .org domain system?), but none like Yahoo and Slashdot. MySQL does not have triggers, stored procedures or views yet. Sub-selects should be out in six months. After fighting with PostgresQL to try to get it to use indexes, rewriting tonnes of queries, and still getting poor performance, I gave up on it. I prefer MySQL with InnoDB. Some of the gotcha's are valid, and others can be found in any database. Forewarned is forearmed. David. - Original Message - From: [EMAIL PROTECTED] To: Mark Warner [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, March 03, 2004 6:51 AM Subject: Re: PgSQL vs MySQL What advantages, besides ease of setup, does MySQL hold over PostgreSQL? It would seem, to me, that the two are close competitors (both in quality, and performance). Are you sure about quality? Check out: http://sql-info.de/mysql/gotchas.html You can check out postgresql's on the same site but they are significally less critical. After reviewing this and talking to some other people i'm switching over to postgresql. I'm sure there is a place with mysql but I don't think i'd trust it for anything critical unless you very confident your developers know what they are doing. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PgSQL vs MySQL
We did benchmarking, with identical schemas on identical hardware. Second, we use InnoDB, which does have constraints, transactions and row locking. Not sure where this significnatly out-dated idea that MySQL has no data integrity comes from, but it's false if you use BDB or InnoDB. It will soon be false for MyISAM as well. I found MySQL (both MyISAM and InnoDB) simpler in that it's setup more closely corresponded to other software I was familiar with. MySQL is as simple as you need to be. You have the option of tweaking advanced variables if you need to. I spent alot of time reading up on PostgresQL performance tuning, and was surprised at how little was known. For example, this page, http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html is linked to frequently in the PostgresQL mailing lists as a good resource for performance tuning. Some of the parameters discussed include random_page_cost, cpu_tuple_cost, cpu_index_tuple_cost, and cpu_operator_cost. The explanation? The default costs are based entirely on anecdotal experience, and are probably not ideal for your system. In fact, many of the parameters have a comment attached stating that you have to run alot of queries to figure out the right balance, and that finding the ... the right balance spot can be hard. (that quote is from the sort_mem parameter comment). Our database evolves fairly quickly (a few changes a month). We don't have time to re-test any query that might be affected by adding columns to a table, altering columns in a table, etc. The optimizer in PostgresQL is it's achilles heel. Do a search on the PostgresQL site for optimizer index and see how often the optimizer makes the wrong choice. I have yet to see a case where MySQL could have used an index but did not. InnoDB is MyQL. MySQL offeres multiple persistence engines - InnoDB, MyISAM (ISAM), heap, and BDB. It is shipped by default with MySQL. David - Original Message - From: James Kelty [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, March 03, 2004 10:51 AM Subject: RE: PgSQL vs MySQL As a DBA I have a few questions about what you said here. You have worked with both PostgreSQL and MySQL, and yet you say that MySQL is 'signifigantly' faster than Oracle? Can you PROVE that? And how is it faster to chase down data problems when MySQL has no native constraints in it data design? Wouldn't you think that since MySQL is 'simpler' to set up and configure that it just lends itself to poor design principles and that you will constantly be fighting with it after a point? Or migrating to something else? Can't argue with the third point at all. In all of this, isn't it really InnoDB that you like, and not MySQL specifically? -James -Original Message- From: David Griffiths [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 03, 2004 10:28 AM To: [EMAIL PROTECTED]; Mark Warner; [EMAIL PROTECTED] Subject: Re: PgSQL vs MySQL As a DBA and someone who has worked both with PostgresQL and MySQL, I think I can answer this knowingly. First, MySQL is significantly faster than PostgresQL and Oracle. Second, MySQL is also a simpler database to set up and configure. Third, the documentation is better, and there are far more third party books out there. Fourth, MySQL has a more impressive list of customers. Yes, there are some large PostgresQL customers (the .org domain system?), but none like Yahoo and Slashdot. MySQL does not have triggers, stored procedures or views yet. Sub-selects should be out in six months. After fighting with PostgresQL to try to get it to use indexes, rewriting tonnes of queries, and still getting poor performance, I gave up on it. I prefer MySQL with InnoDB. Some of the gotcha's are valid, and others can be found in any database. Forewarned is forearmed. David. - Original Message - From: [EMAIL PROTECTED] To: Mark Warner [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, March 03, 2004 6:51 AM Subject: Re: PgSQL vs MySQL What advantages, besides ease of setup, does MySQL hold over PostgreSQL? It would seem, to me, that the two are close competitors (both in quality, and performance). Are you sure about quality? Check out: http://sql-info.de/mysql/gotchas.html You can check out postgresql's on the same site but they are significally less critical. After reviewing this and talking to some other people i'm switching over to postgresql. I'm sure there is a place with mysql but I don't think i'd trust it for anything critical unless you very confident your developers know what they are doing. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http
Re: PgSQL vs MySQL
Curtis Maurand wrote:
:-)
someflag enum('TRUE','FALSE');
Not quite boolean, but it works.
Curtis
On Wed, 3 Mar 2004, Mark Warner wrote:
The thing which bothers me most about MySQL is the lack of a proper
boolean. I don't like having to abstract a tinyint(1) into true or
false. As much of my work involves building applications with Yes or No
questions, I think I am switching to PostgreSQL.
SNIP
To my mind, a boolean is something that can be used in a boolean
context. That is, if flag is a boolean, the following should work as
expected:
SELECT * FROM mytable WHERE flag; # rows with flag = TRUE
SELECT * FROM mytable WHERE NOT flag; # rows with flag = FALSE
If you have to compare the column's value to something, it isn't really
a boolean value.
MySQL, like most programming languages, treats 0 as FALSE and any other
number as TRUE. That means that boolean expressions are evaluated
numerically and compared to 0.
In a numeric context, ENUM columns return the value's position in the
list of allowed values, starting with 1. This means that with the
definition
someflag enum('TRUE','FALSE')
someflag evaluates as 1 when it is 'TRUE' and 2 when it is 'FALSE', both
of which are TRUE in boolean context. Hence, you cannot use someflag in
boolean context and get the expected results. In other words, someflag
looks like a boolean if you view the data, but doesn't behave as a
boolean in queries.
I don't quite know what Mark Warner means by abstract a tinyint(1) into
true or false, but I personally find using tinyint for boolean to be a
simple solution. I define someflag TINYINT, then set it to 0 for
FALSE and 1 (or any other number) for true. Of course, if you're not a
programmer it won't look like a boolean when viewing the data (is that
what you don't like, Mark?), but it will behave as one.
With tinyint you get some added flexibility, which may be an advantage,
depending on your application. For example, if I store the number of
children a person has in the tinyint kids, I can use kids in boolean
context:
SELECT * FROM persontable WHERE kids; #people who have children
SELECT * FROM persontable WHERE NOT kids; #people who don't
One more advantage of tinyints is that, with common programming
languages, they continue to function as booleans in your application.
Another option is to take advantage of the special error value in every
ENUM. If you insert a wrong value, mysql replaces it with '', which is
always 0 in numeric context. So, you could define
someflag ENUM ('TRUE', 'T');
If you insert 'FALSE' or 'F' (or 'false' or 'f') into someflag, it will
get the error value, 0 or ''. When you view the data, you will see
'TRUE' or 'T' for TRUE values and blanks for FALSE, and someflag will
behave as boolean in queries.
If your values are 'Y' and 'N', then, strictly speaking, you don't have
a boolean unless you've defined which is TRUE and which is FALSE.
Assuming you want to treat 'Y' as TRUE, you could define
yesflag ENUM ('YES', 'Y');
Inserting 'YES' or 'Y' (or 'yes' or 'y') will get you the corresponding
entries, which are TRUE in boolean context. Inserting 'NO' or 'N' (or
'no' or 'n') will get you blanks, which are FALSE in boolean context.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PgSQL vs MySQL
Michael,
I couldn't agree more! I didn't even realize there was a boolean issue
until now. I don't really understand the issue of using 0 as false, and
1 as true. The way I view it is that true and false are merely
abstract names for 0 and 1. When the database is accessed using Java, I
don't even see the 0 and 1. For example, using the JDBC API,
pStmt.setBoolean(1, true) command sets the field to 1, and
rs.getBoolean('booleanField') returns true if 1, and false if 0.
So again, I reiterate, what's the issue?
Kevin
On Wed, 2004-03-03 at 11:31, Michael Stassen wrote:
Curtis Maurand wrote:
:-)
someflag enum('TRUE','FALSE');
Not quite boolean, but it works.
Curtis
On Wed, 3 Mar 2004, Mark Warner wrote:
The thing which bothers me most about MySQL is the lack of a proper
boolean. I don't like having to abstract a tinyint(1) into true or
false. As much of my work involves building applications with Yes or No
questions, I think I am switching to PostgreSQL.
SNIP
To my mind, a boolean is something that can be used in a boolean
context. That is, if flag is a boolean, the following should work as
expected:
SELECT * FROM mytable WHERE flag; # rows with flag = TRUE
SELECT * FROM mytable WHERE NOT flag; # rows with flag = FALSE
If you have to compare the column's value to something, it isn't really
a boolean value.
MySQL, like most programming languages, treats 0 as FALSE and any other
number as TRUE. That means that boolean expressions are evaluated
numerically and compared to 0.
In a numeric context, ENUM columns return the value's position in the
list of allowed values, starting with 1. This means that with the
definition
someflag enum('TRUE','FALSE')
someflag evaluates as 1 when it is 'TRUE' and 2 when it is 'FALSE', both
of which are TRUE in boolean context. Hence, you cannot use someflag in
boolean context and get the expected results. In other words, someflag
looks like a boolean if you view the data, but doesn't behave as a
boolean in queries.
I don't quite know what Mark Warner means by abstract a tinyint(1) into
true or false, but I personally find using tinyint for boolean to be a
simple solution. I define someflag TINYINT, then set it to 0 for
FALSE and 1 (or any other number) for true. Of course, if you're not a
programmer it won't look like a boolean when viewing the data (is that
what you don't like, Mark?), but it will behave as one.
With tinyint you get some added flexibility, which may be an advantage,
depending on your application. For example, if I store the number of
children a person has in the tinyint kids, I can use kids in boolean
context:
SELECT * FROM persontable WHERE kids; #people who have children
SELECT * FROM persontable WHERE NOT kids; #people who don't
One more advantage of tinyints is that, with common programming
languages, they continue to function as booleans in your application.
Another option is to take advantage of the special error value in every
ENUM. If you insert a wrong value, mysql replaces it with '', which is
always 0 in numeric context. So, you could define
someflag ENUM ('TRUE', 'T');
If you insert 'FALSE' or 'F' (or 'false' or 'f') into someflag, it will
get the error value, 0 or ''. When you view the data, you will see
'TRUE' or 'T' for TRUE values and blanks for FALSE, and someflag will
behave as boolean in queries.
If your values are 'Y' and 'N', then, strictly speaking, you don't have
a boolean unless you've defined which is TRUE and which is FALSE.
Assuming you want to treat 'Y' as TRUE, you could define
yesflag ENUM ('YES', 'Y');
Inserting 'YES' or 'Y' (or 'yes' or 'y') will get you the corresponding
entries, which are TRUE in boolean context. Inserting 'NO' or 'N' (or
'no' or 'n') will get you blanks, which are FALSE in boolean context.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PgSQL vs MySQL
On Wed, Mar 03, 2004 at 10:51:40AM -0800, James Kelty wrote: As a DBA I have a few questions about what you said here. You have worked with both PostgreSQL and MySQL, and yet you say that MySQL is 'signifigantly' faster than Oracle? Can you PROVE that? For a given set of data and workload, of course you can. And how is it faster to chase down data problems when MySQL has no native constraints in it data design? What about MySQL's constraints are not native? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 171 days, processed 2,369,026,392 queries (159/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: PgSQL vs MySQL
I have an extrememly query heavy site that I tried to switch from MySQL to PgSQL. And after spending literally a week reconfiguring thousands of queries and rewriting code, I finally had the pgSQL version of the site live, but when I had even a trickle of users on the site it was HORRENDOUSLY slow. Now, if I had optimized my code and my queries for another 3 weeks, I may have had something that was usable. But I will take the speed of MySQL and live without some of the niceties of PgSQL. Jeremy -Original Message- From: David Griffiths [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 03, 2004 1:28 PM To: [EMAIL PROTECTED]; Mark Warner; [EMAIL PROTECTED] Subject: Re: PgSQL vs MySQL As a DBA and someone who has worked both with PostgresQL and MySQL, I think I can answer this knowingly. First, MySQL is significantly faster than PostgresQL and Oracle. Second, MySQL is also a simpler database to set up and configure. Third, the documentation is better, and there are far more third party books out there. Fourth, MySQL has a more impressive list of customers. Yes, there are some large PostgresQL customers (the .org domain system?), but none like Yahoo and Slashdot. MySQL does not have triggers, stored procedures or views yet. Sub-selects should be out in six months. After fighting with PostgresQL to try to get it to use indexes, rewriting tonnes of queries, and still getting poor performance, I gave up on it. I prefer MySQL with InnoDB. Some of the gotcha's are valid, and others can be found in any database. Forewarned is forearmed. David. - Original Message - From: [EMAIL PROTECTED] To: Mark Warner [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, March 03, 2004 6:51 AM Subject: Re: PgSQL vs MySQL What advantages, besides ease of setup, does MySQL hold over PostgreSQL? It would seem, to me, that the two are close competitors (both in quality, and performance). Are you sure about quality? Check out: http://sql-info.de/mysql/gotchas.html You can check out postgresql's on the same site but they are significally less critical. After reviewing this and talking to some other people i'm switching over to postgresql. I'm sure there is a place with mysql but I don't think i'd trust it for anything critical unless you very confident your developers know what they are doing. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PgSQL vs MySQL
I know, I get it, I was trying for humor.
Curtis
On Wed, 3 Mar 2004, Michael Stassen wrote:
Curtis Maurand wrote:
:-)
someflag enum('TRUE','FALSE');
Not quite boolean, but it works.
Curtis
On Wed, 3 Mar 2004, Mark Warner wrote:
The thing which bothers me most about MySQL is the lack of a proper
boolean. I don't like having to abstract a tinyint(1) into true or
false. As much of my work involves building applications with Yes or No
questions, I think I am switching to PostgreSQL.
SNIP
To my mind, a boolean is something that can be used in a boolean
context. That is, if flag is a boolean, the following should work as
expected:
SELECT * FROM mytable WHERE flag; # rows with flag = TRUE
SELECT * FROM mytable WHERE NOT flag; # rows with flag = FALSE
If you have to compare the column's value to something, it isn't really
a boolean value.
MySQL, like most programming languages, treats 0 as FALSE and any other
number as TRUE. That means that boolean expressions are evaluated
numerically and compared to 0.
In a numeric context, ENUM columns return the value's position in the
list of allowed values, starting with 1. This means that with the
definition
someflag enum('TRUE','FALSE')
someflag evaluates as 1 when it is 'TRUE' and 2 when it is 'FALSE', both
of which are TRUE in boolean context. Hence, you cannot use someflag in
boolean context and get the expected results. In other words, someflag
looks like a boolean if you view the data, but doesn't behave as a
boolean in queries.
I don't quite know what Mark Warner means by abstract a tinyint(1) into
true or false, but I personally find using tinyint for boolean to be a
simple solution. I define someflag TINYINT, then set it to 0 for
FALSE and 1 (or any other number) for true. Of course, if you're not a
programmer it won't look like a boolean when viewing the data (is that
what you don't like, Mark?), but it will behave as one.
With tinyint you get some added flexibility, which may be an advantage,
depending on your application. For example, if I store the number of
children a person has in the tinyint kids, I can use kids in boolean
context:
SELECT * FROM persontable WHERE kids; #people who have children
SELECT * FROM persontable WHERE NOT kids; #people who don't
One more advantage of tinyints is that, with common programming
languages, they continue to function as booleans in your application.
Another option is to take advantage of the special error value in every
ENUM. If you insert a wrong value, mysql replaces it with '', which is
always 0 in numeric context. So, you could define
someflag ENUM ('TRUE', 'T');
If you insert 'FALSE' or 'F' (or 'false' or 'f') into someflag, it will
get the error value, 0 or ''. When you view the data, you will see
'TRUE' or 'T' for TRUE values and blanks for FALSE, and someflag will
behave as boolean in queries.
If your values are 'Y' and 'N', then, strictly speaking, you don't have
a boolean unless you've defined which is TRUE and which is FALSE.
Assuming you want to treat 'Y' as TRUE, you could define
yesflag ENUM ('YES', 'Y');
Inserting 'YES' or 'Y' (or 'yes' or 'y') will get you the corresponding
entries, which are TRUE in boolean context. Inserting 'NO' or 'N' (or
'no' or 'n') will get you blanks, which are FALSE in boolean context.
Michael
--
--
Curtis Maurand
mailto:[EMAIL PROTECTED]
http://www.maurand.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
