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

91–100 of 164 posts

Re: MySQL - Do Not Pass This Way Again

#91

Post by tveita »

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...

Re: MySQL - Do Not Pass This Way Again

#92

Post by HarrisonFisk »

zzzeek wrote: post #42
taligent wrote: post #37

Earlier quoted context omitted.

You say ONLY_FULL_GROUP_BY was late added. But there are posts talking about this setting in 2002.

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#...

Re: MySQL - Do Not Pass This Way Again

#94

Post by stef25 »

willlll wrote: post #56
mscarborough wrote: post #55

Earlier quoted context omitted.

You're right, offering a solution to MySQL problems in a thread is generally useless. I don't really get what point the author is trying to make. I also don't take detailed performance advice from people who can't keep a blog up. Yeah, must be the wrong backend.

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

I used Postgres on a project and my main concern with it is that whatever GUIs were available were seriously lacking. Phppgadmin is awful.

If you're really pushing its capabilities then there may be better solutions than MySQL but for bog standard applications and websites there is no alternative as far as I'm concerned.

Re: MySQL - Do Not Pass This Way Again

#95

Post by chris_wot »

viraptor wrote: post #26
rosser wrote: post #20

Earlier quoted context omitted.

"Foreign keys are ignored if you spell them certain, common, ways" - another case of "I want to use the wrong syntax, but still get the right answer" Both forms of declaring foreign keys are SQL-standard compliant syntax. That MySQL silently ignores the "inline" version is the "wrong" here, not the syntax. From MySQL's own docs: "Furthermore, InnoDB does not recognize or support “inline REFERENCES specifications” (as…

I understand that and don't have issue with what he most likely meant - only what he wrote. In short - MySQL does not always comply with the SQL standard. That's unfortunate / silly / annoying / inconsistent. I completely agree. But I don't agree with "Foreign keys are ignored if you spell them certain, common, ways". This is not very specific. Common to what situation? People being used to it in another project? It'…

Interestingly, it's not a syntax error.

Re: MySQL - Do Not Pass This Way Again

#96

Post by knightni »

mscarborough wrote: post #80
jacques_chester wrote: post #68

Earlier quoted context omitted.

> 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…

As you described it, the query/caching strategy sounds too complicated. mysql proxy might help you out, or mysql triggers, or plenty of easy indexing strategies. i agree, caching strategies are not easy, but that is not the fault of databases. IMHO keeping your data layers separate is best. if you have a multi-site installation, set some kind of prefix within the app that makes sense to you for each application, befo…

...or he could use a dbms that works properly and save himself the hassle.

Re: MySQL - Do Not Pass This Way Again

#97

Post by waps »

CaptainZapp wrote: post #84
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…

"I'm going to blame myself only - not fileutils or bash - this has nothing to do with the database" I disagree. UPDATE MyTab SET column = 'value' WHERE otherColumn = 1 is a valid SQL statement, while UPDATE MyTab SET column = 'value' WHERE otherColumn - 1 is not and should throw a syntax error and not perform a mass update due to some bullshit auto conversion. What would rile me most is that even if you're a very car…

This is actually a pretty good summary of the whole article. The main point is that mysql works very well, by slightly modifying what you meant into valid statements and doing the best it can. And because what it does depends on the (hidden) type of the data as well as what you actually ask it to do ... it sometimes does things you didn't intend.

It reads a lot like a static typing versus dynamic typing debate.

It's easy to find yourself on either side of this debate. If I program in javascript for a few hours I'm ready to attack any dynamic typing proponent with an axe. If I attempt to write something bigger in Haskell (or try to make something run faster), I am starting to wonder if being bludgeoned to death with an axe is too easy a death. If I try to program anyting in Coq it gets a lot worse.

Re: MySQL - Do Not Pass This Way Again

#98

Post by knightni »

toast0 wrote: post #71
jacques_chester wrote: post #35

Earlier quoted context omitted.

Or "joins are too slow". Well, yes, they can be. But some database systems have smarter plan builders than others. Take, for example, this gem: > Joining and ordering by rows from multiple tables often forces MySQL to dump the whole join to a temporary table, then sort it -- awful, especially if you then use LIMIT BY to paginate the results. This describes just about every standard page-with-comments schema ever devi…

Is it MySQLs fault that wordpress asks it to do something that's hard? LIMIT OFFSET queries are always going to be hard because you have to generate lots of rows only to throw them away, but there are ways to organize the data, and write the queries so that you avoid temporary tables. I don't think Wordpress is MySQL specific; and maybe they use an ORM layer anyway, but if you want to get the most performance out of…

> LIMIT OFFSET queries are always going to be hard because you have to generate lots of rows only to throw them away

In fairness, that often depends upon the intelligence of the planner. A lot of sort-limit-offset queries can be reasonably easy and not generate too many rows if the planner works correctly and you have the right indexes.

edit: Assuming you're mostly picking up early pages as opposed to getting late ones, anyway - which is usually the case for, say, a comment system.

Re: MySQL - Do Not Pass This Way Again

#99

Post by mercurial »

viraptor wrote: post #26
rosser wrote: post #20

Earlier quoted context omitted.

"Foreign keys are ignored if you spell them certain, common, ways" - another case of "I want to use the wrong syntax, but still get the right answer" Both forms of declaring foreign keys are SQL-standard compliant syntax. That MySQL silently ignores the "inline" version is the "wrong" here, not the syntax. From MySQL's own docs: "Furthermore, InnoDB does not recognize or support “inline REFERENCES specifications” (as…

I understand that and don't have issue with what he most likely meant - only what he wrote. In short - MySQL does not always comply with the SQL standard. That's unfortunate / silly / annoying / inconsistent. I completely agree. But I don't agree with "Foreign keys are ignored if you spell them certain, common, ways". This is not very specific. Common to what situation? People being used to it in another project? It'…

I can understand MySQL not being 100% compliant with the SQL standard. What I have trouble understanding is why it can't be bothered to throw an error. Imagine if your programming language silently ignored some constructs? But it seems to correlate with MySQL's philosophy of being good at silently ignoring problems :(

Re: MySQL - Do Not Pass This Way Again

#100

Post by simonw »

taligent wrote: post #29
simonw wrote: post #7

Earlier quoted context omitted.

We migrated Lanyrd from MySQL to PostgreSQL, primarily to reduce the pain involved in running schemes alterations against large tables but we've since been enjoying some if the more advanced PostgreSQL features (pg_trgm indexes for example). You can read about our migration here: http://lanyrd.com/blog/2012/lanyrds-big-move/

Just wanted to confirm something. You say you have no single point of failure but you are running everything in one data center ?

That was slightly loose wording in the article: yes, the single data center is currently a single point of failure (though we can spin up a replacement stack elsewhere very quickly). Andrew was referring to the way we had rearranged our software stack to reduce the SPOFs (our previous architecture had a single load balancer for example).
Post reply on HN