Comments for A Little Noise

Comment on Extract Regular Expression Match by Matt

Matt — Tue, 10 Jan 2012 23:01:43 +0000

Agree with Eric. Bookmarkin’ it.

Comment on Extract Regular Expression Match by Eric Lewis

Eric Lewis — Tue, 10 Jan 2012 22:57:34 +0000

This is awesome. Period.

Comment on Quiz: A More Perfect UNION by Scott Noyes

Scott Noyes — Thu, 10 Nov 2011 05:47:44 +0000

Not a network issue; this is on localhost.

Storage engine of table `t` can be either MyISAM or InnoDB, and we’ll get the same results. But storage engines and temporary tables are nevertheless key to the quiz!

Size is important, but size of what? For my test, I had a million rows in `t`, but I can reduce that to 65K, and the first approach still takes over a minute, while the second approach with the UNION takes a quarter of a second.

Comment on Quiz: A More Perfect UNION by Shlomi Noach

Shlomi Noach — Thu, 10 Nov 2011 04:09:13 +0000

I’m guessing, as there is not much info:
The UNION ALL query will first write your data into temporary table; only then will the data return to you.
This means that if your result set is huge, and your network is slow, query #1 suffers from the time it takes to send the results over to you; query #2 does not, since the temporary table releases the table.
Can you try SELECT SQL_BUFFER_RESULT d FROM t; to see if this is indeed the case?

Otherwise more is needed; myisam? innodb? write contention? size?

Comment on Quiz: A More Perfect UNION by Scott Noyes

Scott Noyes — Thu, 10 Nov 2011 00:44:09 +0000

Caches are not relevant here. I can run these two versions in either order, multiple times, and performance is the same as initially stated.

Comment on Quiz: A More Perfect UNION by David

David — Wed, 09 Nov 2011 22:38:21 +0000

Simple answer: Caches

If it is innodb, your first one loaded the table into the buffer pool. If not, most likely, you have disk caches, os caches, mysql caches, et al. These would have been populated the caches between the data and you.

Comment on Quiz: A More Perfect UNION by Quiz: A More Perfect UNION « Fast Ninja Blog by Freelanceful – Web Design | Coding | Freelancing

Wed, 09 Nov 2011 22:20:07 +0000

[...] PlanetMySQL Voting: Vote UP / Vote DOWN Original Source: Planet MySQL [...]

Comment on Show all grants by Shlomi Noach

Shlomi Noach — Tue, 18 Oct 2011 18:52:56 +0000

Ah, I see. Thanks

Comment on Show all grants by Roland Bouman

Roland Bouman — Thu, 13 Oct 2011 21:14:16 +0000

Shlomi,

I think Scott is just salvaging the snippets from MySQL forge.

Comment on Show all grants by Shlomi Noach

Shlomi Noach — Wed, 12 Oct 2011 19:49:45 +0000

Allow me to suggest using the view sql_show_grants, part of the common_schema instead.
I believe it is better than the above code, for the following reasons:
1. The above code results with multiple results set
2. It is a stored procedure, so you can’t do SELECTs on it
3. In particular, you can’t do WHERE, e.g. WHERE user = ‘particular_user’ etc. Unless you code it into the proc, but then there’s so many other issues which must be coded…

sql_show_grants is a view which lists down using a SELECT query the GRANT statements for all users. You can filter based on specific user.
Better, it’s sql_grants companion can also list down the REVOKE statement for each user. And you get even more details per entry; the doc is very detailed.

There is a lot more code in common_schema that answers for some of the posts you’ve posted today; I urge you to take a look!

Regards,
Shlomi