OT: I'm stunned how bad MySql is after using Postgres

[Joe Barnhart]

I'm talking about the query optimizer here...

I had been running my test site on Postgres for some time and getting pretty good performance.  I switched to MySql because it is so much more ubiquitous among vendors who sell SAAS for web deployments.  I can find tons of providers of MySql instances while Postgres instances are very hard to find (and thus tie you to a very small pool of competitors).

My first "oh sh!t" came with a query that does grouping and counting of duplicate rows across two tables.  A query that took a second or two in Postgres now takes 30-60 seconds in MySql.  Did I screw up and forget to provide the index?  Nope, its there.  But -- in a big surprise -- I discovered MySql IGNORES the index and resorts to a full table scan because I have "where xx IN ()" in the query.  Are you kidding me??  MySql can't handle "IN" and "GROUP BY" in the same query??!?  It won't even let me FORCE it to use the index.  Nothing I have been able to Google has improved this query.

I've since discovered numerous other examples where the query optimizer of MySql is significantly behind Postgres, but in most cases I've been able to rewrite the query to recover most of the performance loss.

I'm just sitting here shocked that MySql has so completely dominated the database world with such an excremental query optimizer.  Lord how I wish more people knew about Postgres so it would be the popular choice instead of MySql!

(Apologies in advance to any MySql partisans.  I'm sure you are highly offended by my dismissal of your One Chosen Database, but I have to call it as I see it.)

-- Joe

[Jim S]

What version of MySQL?  We had similar problems with 'IN'.  Moving to 5.6 fixed it.  However, on some platforms, 5.5 is still the standard for installs.  Check out 5.6 if you have access to it to see if that helps.

-Jim

[Niphlod]

BTW, you just joined two groups I'm in : "postgresql lovers" and "mysql haters".
I'm a taddle biased too (mainly for hiccups seen here and there, also while working on web2py), but mysql doesn't really cut the deal of behaving like a serious database should.
I'm happier with SQLite, and that's pretty all I'll say about mysql.

NB: in the real life, among other things, I manage (enjoiying) a very large farm of MSSQL servers.

[Joe Barnhart]

Hi Jim --

Since I'm just starting with MySql, I'm using the latest and greatest 5.6.  The newer 5.7 is still "testing" so I have not tried it.  My site needs to be on official versions of stuff.

[Joe Barnhart]

Niphlod --

I guess I was expecting more parity between the two since MySql has such a dominant position (at least here in the US).  I am shocked at how different they are performance-wise.  But my requirement of being able to deploy on cloud services easily is outweighing my disappointment in MySql.

It just goes to demonstrate how it's not always the best product that wins the popularity contest.  (But then, as a voter in elections here, I'm used to that... )

-- Joe

[Jim S]

Shoot, I was hoping that information would help you out.  Certainly made drastic improvements in my case.  Let the hatefest continue!!  However, I'll have to skip this one, MySQL has done very well for me over the years..

-Jim

[Niphlod]

On Friday, March 6, 2015 at 11:58:49 PM UTC+1, Joe Barnhart wrote:

Niphlod --

I guess I was expecting more parity between the two since MySql has such a dominant position (at least here in the US).  I am shocked at how different they are performance-wise.  But my requirement of being able to deploy on cloud services easily is outweighing my disappointment in MySql.

It just goes to demonstrate how it's not always the best product that wins the popularity contest.  (But then, as a voter in elections here, I'm used to that... )

-- Joe

hehe.....got it but then you'd be forced to cgi too (e.g. over gunicorn or nginx+uwsgi).
Just My 2 Cents: "being able to deploy wherever" really crashes with "I have to spend 2 hours a week fixing queries/tuning databases" as soon as you hit the "6 month on a project" line, and your users are still waiting for that page to be shown in under 500ms.
In the end, you saved money you didn't throw at, e.g., heroku, but you spent 2 of your precious 40 hours being angrier for no reason. And you lost several chances on making your app more awesome.
IMHO world in 2015 is far better than 2014, and "good surprises" keep coming almost every month.
With "players" as heroku, pythonanywhere, appfog, dotcloud, or - not really paas but who gives a damn - stackato, cloudfoundry on aws.....or even awesomer "please manage my environment" solutions like cloud66...it makes the "cost" argument less compelling.

[Joe Barnhart]

Hi Jim --

Please, whatever you do, DO NOT ever try Postgres!  You will be ruined for MySql...  :-P

-- Joe

[Jim Steil]

Nice one.  Evidently we're developing very different types of apps.  I rarely hit table sizes greater than 1M records.  MySQL performs really well for me.  Yes, I've had to code around some performance issues (the IN clause specifically), but that is trivial.  I've only ever worked with Postgres once and didn't like it. As I recall (it was quite a few years ago) there was more management of the db that had to be done.  MySQL is pretty much hands-off, create it and let it be.  But, like I said, I have very little experience with Postgres.  To each their own I guess.

Best of luck with the new app!

-Jim

--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/JUUax7GjxYo/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.