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 On Friday, March 6, 2015 at 3:17:09 PM UTC-6, Joe Barnhart wrote: > > 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 > > -- 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 the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
