Fog Creek Software
Discussion Board




MySQL Adds Subselects

Finally it took like 5 years....


"MySQL Version 4.1 includes support for SQL subselects, also called subqueries or nested queries, a powerful feature that lets users search complex data with ease and efficiency. With subselects, users can achieve query consolidation by nesting one query inside another query, creating a resource otherwise unavailable for searching in a single statement. Subselects allow users to query within a result set, creatively limit the result set or correlate results with an otherwise unrelated query in a single call to the database."

http://www.mysql.com/press/release_2003_05.html

Matthew Lock
Sunday, January 26, 2003

It's supposed to be significantly faster too, due to built-in caching of results for similar SQL queries.

eWeek magazine did an elaborate benchmark study last year with mySql (pre-production version 4) vs Oracle vs MS Sql Server.  Oracle and MySql were neck and neck.

http://www.eweek.com/article2/0,3959,293,00.asp

Will Glass-Husain
Sunday, January 26, 2003

Boy, MySql is really getting impressive.

It has been what, not even one year since MySql got the InnoDb table extensions. That means that referential integrity (RI) stuff like Cascade deletes is now available with MySql.

I often had people ask me why I am not considering MySql for my applications.

      My answer *was* always very simple:

      MySql is not really a relational database!.

In other words, if you delete a invoice, then the invoice details are NOT deleted for you.  I suppose the old FoxPro/clipper/dbase folks got along very fine without this delete/RI feature also, but software is WAY MORE sophisticated today. Clients demand more features for each  dollar spent on me. That means I should not as a developer have to write code to delete those child tables.

MySql finally did get RI, and that is a huge leap forward.

RI stuff like cascade delete on child tables means that I can add new code, and even add new data edit screens to an application, but not have to worry about other parts of the application that currently exists. So, if I add a few more lookup tables to a human resource edit screen, then the  other screens (or code)  in current use that delete the Employee will NOT have to be modified to delete those new child tables I just added.  In other words, you can MUCH more easily add new tables and features to a * EXISTING * system when you have RI. I suppose in a perfect world, a employee object would be made (but that is not the norm).

Simply put, engine level enforced RI is really needed in any modern development environment. (heck, even the JET engine from MS has RI). I simplye cannot design, and live without it. (dispite my years in FoxPro).

So, my #1 complaint about MySql was lack of RI. It now has this feature.

My 2nd complaint was the fact of no sub queries. They just added that now.

So, my * major * two reasons for not using MySql have just vanished.

The graphical front end manager that is included with MySql is also very nice (I have only used the windows version).

Hence, as a result, I have for last few months been testing and playing with MySql. It is looking like  a winner for me right now.  I am in the process of testing some of my applications, and it is very likely that I will use it in my next project.

The only real features now missing is a server side language, and views.

However, you can run a script (sql commands), and set results into a variable. You can then run some sql commands using these vars. Hence, it is not a full programming language like t-sql, but it is still very useful.

With the addition of sub-queries, MySql has broke through the ice for me.

My only next step to figure out what my responsibilities are from a licensing point of view. I am certainly still going to charge a per seat cost for my software, but looks like I don’t have that problem with MySql.  It will mean more money in my pocket.

The ease, and freedom that I can download, and use the engine for testing is also what is getting me hooked.

Albert D. Kallal
Edmonton, Alberta Canada
Kallal@msn.com

Albert D. Kallal
Monday, January 27, 2003

Albert, check out PostgreSQL. It has always had Subselect's, Referential Integrity, Views, Stored Procs, Triggers, Transactions, Outer Joins, Write Ahead Logging, Concurent Version Control, Sub-row Locking and most of what peoples complain is/was missing in MySQL. It's also getting to be nearly as fast as MySQL for most situations (In my experience, the more complex the work, the less MySQL's advance is and when things get stressful it often pulls ahead of MySQL.)

PostgreSQL is open source licensed and the only major feature missing is Replication and there are some open source projects and supported commercial Replication solutions (ie: eRServer v1.2 from PostgreSQL, Inc.)

I've been using it a lot and I find I have a hard time to touch MySQL when I must, I just take things for granted and then find myself coding around them when I really shouldn't be.

Alex
Monday, January 27, 2003

Alex,

PostreSQL is great - for non-Windows users.  Unfortunately the Cygwin-hack version of PostgreSQL isn't something that's simple to set up, nor would I ever run it as such in a production environment.  True enough, Linux is free, but learning to administer it effectively is not free (in time) - I think this is why MySQL is more popular than PostgreSQL.  Otherwise, I agree - it's a much better database than MySQL hands down.  But then again, so is Firebird (free/shared-source version of Borland Interbase) that's available at http://firebird.sourceforge.net  - you can also find a decent GUI frontend for it online (search google for "IBOConsole")

GiorgioG
Monday, January 27, 2003

I have often considered using PostGres.

One the reasons for MySql’s incredible popularly over PostGress is the ease with which each version is made available to the public.

In other words, Microsoft has always realized make it not only easy, but also available.

Easy + available is much better then Technically better and not as available!

Hence, from a market point of view:

(Easy + available) >=  (Technically better + not as available).

When I go to the MySql site, I have NO problem finding what I need. Lets see, a graphical GUI (table manager), ODBC drriver, and Mysql database engine. All are easy to find downloads, and this is especially so for windows users. I want the versions that will run on my notebook running win98/ME.

They even supply a small little graphical database utility to start, and stop the database engine (it runs in the system tray, again a very nice touch).

Microsoft and Macdonald’s did not always have the best product. However, in some areas they are really good. Microsoft did good job with Excel, and Macdonald’s did a good job with their fries.

So, Macdonald’s will cut back on quality, and spend their time opening 5 locations compared to a higher quality burger place that only opens one spot. Who will win in the end?

All of the MySql downloads were a walk in the park. The install programs were also very easy.  From start to finish, the whole process is real easy, and quick. Remember, in retail, location is king. In the web, that translates into making things easy to find.

For PostGress, I am not really sure where to start. I mean, what version I can download to run on my windows 9x notebook?.  Anyway, I want to run, test, and setup a database engine. MySql was unbelievably easy to find, install and run.

The very first time I launched Excel, and fired up the built-query builder in Excel, I was able to grab data from MySql. It just worked right out of the box.

Right now many people are questioning as why MySql is Soooo much more popular then Prostgress. The popularity of MySql is soaring right now. I can’t say the same for Postgres.

MySql is spending a good deal of time on this issue of making things easy to get MySql. They are spending time on making it easy to install it, and get it up and running.

I have been meaning to try Postgres. I will probably just have to hunt around, and find that nice download site that easily lets me find the 2 or 3 above downloads that are sooo easy with MySql.

Just last night while watching TV and loafing on the couch, I downloaded MySql, MyODBC, and the graphical table manager. The whole process of * F I N D I N G *  and down loading the files took less then 5 minutes (start to finish!). I get good download speeds in the 500-600 kbs range.  Hence, I had the whole thing up and running in just few minutes.  (a 13 meg MySql download is not even a minute for me).

The fact that it is so easy to install and * play * with MySql is really what is helping its popularly here.  MySql has a definite angle towards users. (and that is NOT usually the case of open source software).

It reminds me of first using a palm pilot. Very light weight, fast, simple, and easy. Now I am hooked on the palm. Now that we have all used a palm pilot, we are ready for something more complex.  However, by the time I get a round to moving to a cool windows based pda with a nice mpeg player, the palm will have most features I want anyway, and I can still keep much of what I have learned already.

This is exactly what is happening with MySql vs Postgres.

Hence, you would think that we all should be ready to jump to Postgress.

The problem is that we are not making the jump from MySql to Postgress because those features we need/want are in the pipe for MySql.

MySql is reaching critical mass right now. Postgres is not.

The MySql folks are doing a much better job of getting the name out, and also a much better job from a ease of use, setup and install. The user experience in installing and trying out MySql is much better then is postGres.

This concept of user experience is CRTICIAL HERE. Products that are technically inferior often win in the market place. (apple vs MS).

In fact, I actually found that setting up MySql was * EASIER * then was installing the free MDSE engine from Microsoft that is included on every office CD! Imagine that, but my user experience of installing MySql is better then the free MDSE engine on the office CD! (yikes!).

Further, I can’t use the MDSE engine without the Enterprise tools, or creating a ADP project in ms-access.  Problem is right now, is I don’t want to use a ADP project in ms-access. So, where are the tools to setup and manage the MDSE engine? (answer: get you have to get your hands on the enterprise tools)

It am betting it will take more much more then just 5 minutes to find the above 3 tools and downloads for PostGres. In fact, as I type this, I browsed over to the downloads section of the www.postgres.org. That sends me to a ftp site, and at that point it is not quite clear at all which ftp dir I need to browse to for the windows install. In fact, I don’t see where the full pre-built install is at all. It has taken me more then 5 minutes to find a install. That is simply too long. We could very well take that attitude that I need to make more effort here (and I will). However, it is very obvious that getting, finding and installing MySql is breeze.  MySql is eating Postgress for lunch in this area.

The palm caught everyone off guard due it being easy, and available. That is why MySql is growing .

Easy, and simple is good!

Now, the graphical management tools for MySql are very basic. However, again, that GUI installed on my windows notebook no problem, and they are very nicely laid out. I am impressed! They have pre-built installs for windows, and I don’t have to go and hunt down some cgywin tools etc and build anything. Having some GUI tools to create tables/indexes and databases is really nice. I did start out using MySql via the command line. However, right on the MySql downloads page was a download for a GUI manager. I tried it out, and boy, very nice.

Why use a command line prompt when everything else I have is GUI? Heck, they even copied icons for the query builder from ms-access! Gee, these guys are really smart!! This shows that the MySql people understand the fundamentals of software marketing. You go with what works! You go with what is familiar. Lifting the icons from ms-access is simply brilliant!

Create tables, create indexes, and a query builder. A nice easy treeview control was used to browse and select the database and tables. Not much to go on is it?

However, the above fact also makes it incredibly easy to setup and run MySql. The dumb thing just works, and it is easy and simple just like my Palm.

Of course the new palms pilots are also really getting loaded up with new features, and so is MySql. If they add a ER tool that MySql GUI, then they have a killer product.

MySql creates a very positive experience out of the box.  They are on a roll!

Hence, get the product out, even if you don’t have all the features. However, what you DO HAVE better be easy. Also, what you do have better be of good quality (so, we are not really fast food here!!).  Also, you want the users to have some positive feedback right away.

Human brains LOVE feedback. Hey, this thing is cool, hey, this thing is useful! Just seeing the familiar ms-access icons in the MySql query builder was a hoot!  These guys are not scared to take, or use ideas from anywhere. And what is familiar is good!

Software MUST create a positive experience out of the box. Call it fluff, call it eye candy, call it a very sad day, but it is the truth that a positive experience wins the customer.

I remember many years ago I got a Apple II computer. One of my friends got a Atari 800. Both Apple and Atari had a 6502 processor, but the Atari was MUCH BETTER from a technical stand point (better sound, and it even had graphic sprites). However, the Apple II came with a few cassettes and some neat games. I could rummage around the house and find a cassette recorder and plug it in to the apple II. Next thing you know we are playing the game lemonade. It was a really cool experience. There was also a few other neat games (breakout, and a few more). Next day I ran out and purchased Sargon II chess (again, on cassette).  It was amazing to see a computer play chess!  It was also in hi-res graphics.

The Atrai 800 was a much better PC, but it had no software, and no neat games included in the box. The user experience when we went over to my friends house who had just bought the Atari 800 was very poor. In fact, after playing around with my Apple, the Atari was a complete let down. A few days later, my friend returned the Atari, and got a Apple II.

Just one year later, virtually NO one bought and used the Atari 800. By that time, we were out buying floppy disk drives for our AppleII’s  at $800 a pop. By then, we were having parties in the neighborhood to swap Apple games. We would also stay up late to crack  protected apple games too. Hey, I learned 6502 assembler!. Heck, it had a mini assembler built right in! The Atari was a much better PC, but the INITIAL user experience was poor due to no good initial software being packaged.

Now, I am eager to try Postgress.  I just need to find that nice download site that gets me what I need to try it on my notebook computer.

If Postgress can create a positive experience like my Palm Pilot, The Apple II, and Macdonald’s French fries, then I will give it a try.  MySql sure did create a positive experience for me.

In reading the above, I just realized a number of things I will do for my own software. I have a demo coming up this week. Hum, with a bunch of good test data, I will certainly make a good impression.

#1 will be to ensure that the user can try/test useful things RIGHT AWAY.  Any kind of pre-configuring and pre-data entry will hurt this experience. I will actually phone them and get some of the products/price lists and pre-enter them into the computer BEFORE I bring him a demo.

I now realize how important that first impression of ease of use is!

I have to start thinking like a customer more often!

When I try and use MySql, or Postgres…I am in effect still a customer, even if it is open source and free.

You see, the above will also help me pick what product is going to be a winner in the marketplace. Choosing a database can be large investment in time. I am going to pick the winner because that is one that is most valuable. My clients will have heard about MySql…and if they have not…they will soon…

Winners don't pick winners by accident....

Albert D. Kallal
Edmonton, Alberta Canada
Kallal@msn.com

Albert D. Kallal
Monday, January 27, 2003

--
It am betting it will take more much more then just 5 minutes to find the above 3 tools and downloads for PostGres. In fact, as I type this, I browsed over to the downloads section of the www.postgres.org. That sends me to a ftp site, and at that point it is not quite clear at all which ftp dir I need to browse to for the windows install
---

Well, if you manage to find them, just tell me where! ;)
As far as I know, there is _no_ "native" windows port of PostgreSQL. As a matter of fact, the survey at the front page asks:

"User Survey

What would attract the most new PostgreSQL users?

Win32 Port
<more choices>
"

There is a way to run Postgres on Windows, using the Cygwin Port (it's supported by the Cygwin people), but no "point and cick" installer ;)

Of course, MySQL internally is just a "cygwin" port, but they've packaged it nice (and it runs better on "native windows" than downloading and compiling sources under Cygwin... at least, the last time I tried it was better the native W32 port).

So I think that if you want to try PGSQL on W9X, you can.

1) Get cygwin (on www.cygwin.com)
2) Select PostgreSQL in the packages to install window
3) Ready to go ;)

Just my 0.003

Javier
Tuesday, January 28, 2003

The cygwin version of postgres is a nightmare. I managed to get it working, but it broke ssh on my laptop. And cygwin is just so alien to windows users, it is very difficult to get anything done. Heck, it took me a while to just figure out where it put my files!

Chris Tavares
Tuesday, January 28, 2003

Albert:
  "...referential integrity (RI) stuff like Cascade deletes is now available [before]...if you delete[d] [an] invoice, then the invoice details are NOT deleted for you..."

I wouldn't say that Cascade Deletes are what makes a database "relational". SQL Server only relatively recently got them - and one wouldn't say that it isn't a "proper" relational db.

Duncan Smart
Tuesday, January 28, 2003

... I've always felt that RI is more "the other way round" -- ie, the db guarantees that your Foreign Keys are always referencing a valid Primary Key... and it won't allow you to delete the rows on the "one" side of the relationship, whilst records exist on the "many".

Cascade Deletes are welcome sugar, but I don't feel they're the defining point of RI.

Duncan Smart
Tuesday, January 28, 2003

Just to correct what Javier said, the Windows port of MySQL is a native port, compiled with Visual Studio. (With the minor exception of the mysqlc.exe binary, which is a command-line client compiled with cygwin to take advantage of the readline library.)

Jim Winstead
Tuesday, January 28, 2003

Albert,

Have you considered starting your posts with an executive summary? I hope you do not mind me asking but given the amount of time I want to spend on this board your posts are just getting too long to read (and I do want to read them).

Just me (Sir to you)
Wednesday, January 29, 2003

*  Recent Topics

*  Fog Creek Home