Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

MySQL - Do Not Pass This Way Again

grimoire.ca

Post reply on HN
Print view View original

121–130 of 164 posts

Re: MySQL - Do Not Pass This Way Again

#121

Post by michaelmior »

sergiosgc wrote: post #104
toast0 wrote: post #76

Earlier quoted context omitted.

MySQL's query planner is actually pretty good, but there are some things that are just not going to be fast; most of which is documented, almost all of which can be seen with describe select ...; if it uses a temporary table, it's probably necessary, and it's definitely going to be slow once you have enough rows. If WordPress uses any of these things, it's not MySQL's fault.

This may have been true ten years ago. Not anymore. Mysql query planner is dumb as a rock. Easy example: instead of referring to a table in FROM, try using a sub query with SELECT * from the same table. It's an easy reproduction of the planner failing to process the query tree. It should result in the same execution tree. It results in a temporary table being created, as an identical copy of the original table. Befor…

Fortunately there are big improvements coming to the specific case of subqueries in MySQL 5.6.3 [1]. MariaDB (a MySQL fork) also has several further optimizations [2].

[1] http://dev.mysql.com/doc/refman/5.6/en/from-clause-subquery-... [2] https://kb.askmonty.org/en/subquery-optimizations-map/

Re: MySQL - Do Not Pass This Way Again

#122

Post by zorlem »

hpaavola wrote: post #83

Instead of writing these rants go and build a WAMP like package with PostgreSQL or other alternatives. That might actually do some good.

> Instead of writing these rants go and build a WAMP like package with PostgreSQL or other alternatives. That might actually do some good.

You mean like WAPP, the package installer provided by BitNami [1]?

I don't see a problem with a well-argumented rant, IMO it's quite helpful as it sparks such discussions, let people improve their knowledge and make better informed decissions, and, not the least important, forces a vendor to work towards improving their software.

[1] http://bitnami.org/stack/wapp

Re: MySQL - Do Not Pass This Way Again

#123

Post by TazeTSchnitzel »

dendory wrote: post #112

I started 7 years ago with SQLite and still have all my sites and webapps running on that. Works wonderfully for me. Meanwhile I must have seen close to a dozen data storage systems become popular then be replaced by the next big thing, from MySQL to NoSQL and everything in between.

I love SQLite. It's simple, it doesn't make a fuss, and it does what you'd expect. And better yet, since it's a single file application database instead of a database running on a server, support for it is built in to Python, and you using it is as simple as an import statement and .connect() - no server to configure.

Re: MySQL - Do Not Pass This Way Again

#124

Post by viraptor »

epo wrote: post #116
viraptor wrote: post #16

I'm really not sure what to think of that article. On one hand side, I definitely agree with it and I've experienced many issues with MySQL. On the other, there are so many... strange points, it's hard for me to trust the author about the parts that are new to me. Things I've found weird so far are: - "my favourite example being a fat-fingered UPDATE query where a mistyped = (as -, off by a single key) caused 90% of…

The first thing you found wierd relies on selective quotation, you omitted "because of implicit string to integer conversion". The second is an apologist "but its well documented" defence. Your third point is changing the subject, he was criticising implicit type conversion, you again igore that. The fourth point is not about wanting to use the wrong syntax it is about MySQL acepting valid syntax and ignoring it. So…

I'm as far from being mysql fanboy reasonably possible, so no, that's not it. What I'm trying to say is: mixing subjective and objective criticism makes the argument weaker. Don't complain that you lost your data due to a typo (statement worked as designed, you had backups, this was a test database and you'd never do that in production anyway - right?) A complaint about the lack of consistency and not adhering to standards is much stronger and still true.

Re: MySQL - Do Not Pass This Way Again

#125

Post by mdellabitta »

mscarborough wrote: post #57
mdellabitta wrote: post #54

Earlier quoted context omitted.

This is an ad hominem argument. His statements about MySQL should be judged on their own merits, regardless of the load times of his blog.

No, it's not. If you're going to be a performance authority about web technology, you should know how to configure the open-source and very well-documented code you are using.

And so you assume that the author of the article doesn't know how to optimize his or her blog, ignoring the possibility that he or she does, but didn't do it for intervening reasons.

Still has no bearing on the veracity of statements made in the article.

Re: MySQL - Do Not Pass This Way Again

#126

Post by ansgri »

mscarborough wrote: post #61
willlll wrote: post #56

Earlier quoted context omitted.

How could you possibly not get the point? It's the first two (2) sentences "Considering MySQL? Use something else."

I use MySQL and Postgres, depending on the project. What other option is so obvious that you can't even name it?

SQLite is so obvious, and in many cases it's just enough. Or, hsqldb if you're into Java.

Re: MySQL - Do Not Pass This Way Again

#127

Post by EugeneOZ »

jacques_chester wrote: post #68
mscarborough wrote: post #63

Earlier quoted context omitted.

If you have to be a jerk about it, it may interest you that relatively static blog content can be cached on the server. I'm genuinely interested to learn which use cases are more dependent on query planning than caching methodologies.

> I'm genuinely interested to learn which use cases are more dependent on query planning than caching methodologies. I'll reply to this separately, it's a good discussion to have. My basic problem is that caching comes with a coordination cost and I prefer the originating data source to be as performant as possible. My own use case is a small Wordpress multisite installation. Even with a relatively trivial amount of…

> But like I said, caching comes with a coordination cost.

And what? It's the reason to not use caching? ridiculous

Re: MySQL - Do Not Pass This Way Again

#128

Post by zzzeek »

HarrisonFisk wrote: post #92
zzzeek wrote: post #42

Earlier quoted context omitted.

but only added in version 5 (I was using it back with version 3 in the late 90s): http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html#... > Do not permit queries for which the select list or (as of MySQL 5.0.23) HAVING list refers to nonaggregated columns that are not named in the GROUP BY clause.

You quote version 3.23, yet talk about subqueries which didn't get added until version 4.1. ONLY_FULL_GROUP_BY was added in 4.0 but only applied to select list columns, the 5.0.23 mentioned above applies to the HAVING clause list not allowing them as well: http://dev.mysql.com/doc/refman/4.1/en/server-sql-mode.html#...

the point is, it's outrageous MySQL even does GROUP BY this way and that doing it the "correct" way is not even the default.

Re: MySQL - Do Not Pass This Way Again

#129

Post by zzzeek »

tveita wrote: post #91
zzzeek wrote: post #32

this is my favorite MySQL "decision", that the GROUP BY keyword by default (that is, unless you turn it off with the late-added magic flag ONLY_FULL_GROUP_BY) will gladly select an essentially "random" (well, the first row based on INSERT order, which in SQL is as good as random) row for you: mysql: create table data (token_a varchar(10), token_b varchar(10)); Query OK, 0 rows affected (0.05 sec) mysql: insert into d…

I have encountered situations where this is convenient, and I have yet to see it cause any bugs or problems. In the places I have seen it used, the 'arbitrary' column typically has the same value for the entire group, e.g. for efficiently selecting distinct texts based on their hash values. PostgreSQL has a similar feature using SELECT DISTINCT ON: http://www.postgresql.org/docs/9.2/static/queries-select-lis...

> In the places I have seen it used, the 'arbitrary' column typically has the same value for the entire group, e.g. for efficiently selecting distinct texts based on their hash values.

I agree that case is convenient. But I've seen it being used to actually pick a random row, where a different row would have a different result (such that the output of the program would definitely be different), and it's clear the developers who wrote it didn't fully understand this was going on. I wrote it up in a report for this particular client but they didn't seem to want to touch this particular very old and venerable code.

Re: MySQL - Do Not Pass This Way Again

#130

Post by exabrial »

>>It's good enough. No it ain't. There are plenty of other equally-capable data storage systems that don't come with MySQL's huge raft of edge cases and quirks.

Actually, it is good enough. Good enough to powere billions of websites. Good enough not to pay for Oracle, DB2, or trying to cram some half-finished nosql mess in where a relational database works better.

MySQL isn't an end all, but please, don't pretend that NoSQL holds all the answers.

Post reply on HN