In case you live under a rock, the MySQL conference starts on Tuesday. There are 8 concurrent tracks of content. Aside from my own talks, here are the sessions I would most like to see.
On Tuesday:
Somebody asked me for a useful example of Oracle 11gR2′s new analytical LISTAGG function that uses a WITHIN GROUP syntax. They’d noticed an update to the askTom that showed how to use it. This post shows how to list values without a displayed aggregation column and how to use a JOIN and GROUP BY clause with the new analytical feature.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
COLUMN list FORMAT A10
COLUMN last_name FORMAT A10
COLUMN names FORMAT A42
COLUMN members FORMAT 9,990
SELECT m.account_number AS account
, c.last_name AS last_name
, LISTAGG(c.first_name||DECODE(c.middle_name,NULL,NULL,' '||SUBSTR(c.middle_name,1,1)||'.'),', ')
WITHIN GROUP (ORDER BY 2) AS names
, COUNT(*) AS members
FROM contact c INNER JOIN member m … |
COUNT() function returns a number of rows returned by a query. In
a popular opinion COUNT(*) should not be used on
InnoDB tables, but this is only half true.
If a query performs filtering on any column, there is no relevant
difference in how COUNT(*) will be executed
regardless of the storage engine. In any such case MySQL has to
look for matching rows and then count them.
In the following queries COUNT(*) can be used without any negative impact on performance:
SELECT COUNT(*) FROM mytable WHERE id = 12345
SELECT COUNT(*) FROM mytable WHERE is_enabled =
1
SELECT COUNT(*) FROM mytable WHERE username LIKE 'a%' AND
is_enabled = 1
The real difference is when no filter is specified in WHERE clause, i.e. when query counts all rows in a table. MyISAM maintains cached row count for each table, so it can always return the value …
[Read more]With the 3rd edition of High Performance MySQL finally complete, I’ve begun work on the 4th edition. As you know, technology moves much faster than printing presses, and a book is outdated very quickly, so this is a never-ending project. I’m also outlining the 5th edition in anticipation of starting it immediately afterwards.
I’m looking for your input on what I should cover in the new edition. Should I discuss MySQL’s intra-query parallelization across multiple CPU cores? Should I explore how cloud computing platforms enable higher performance at lower cost than dedicated hardware? Should I explain the bizarre bug in MySQL’s datetime type that causes it to skip the day after March 31st every year? What are your suggestions?
Further Reading:
MySQL version 4.1 was quite revolutionary. The main reason for that was support for sub-queries.1
However since then MySQL users were rather discouraged to use that functionality, basically due to the implementation’s poor performance and forced to build complicated queries based on joins rather than on subqueries.
Of course you can do some effort to optimize your subquery with sometimes very good results2. Not always it’s easy or even possible if you can’t change the code though.
You’d say it’s not a problem for typical OLTP, web based traffic at all, just don’t use subqueries! That’s true, …
[Read more]O’Reilly authors get 10 copies of their own books for free, and my copies of the third edition of High Performance MySQL arrived yesterday. Now it’s official! It feels nice to actually hold it in my hand.
A few people have asked me about messages from Amazon saying that their ship date has changed. I don’t know anything about that; maybe Amazon just made a wild guess the first time and now they actually know something more realistic. Or maybe the book is more popular than expected? It’s currently at position #10 in the SQL category on Amazon, which seems pretty good to me. No “DaVinci Code” to be sure, but not bad for a technical book.
Further Reading:
[Read more]I’ll be speaking at the Shenandoah Ruby Users Group Monthly Meetup tomorrow in Harrisonburg, Virginia. The topic is “Seven Things To Know About MySQL Performance.” See you there!
Further Reading:
Wow, I completely forgot to advertise this. I’m speaking Saturday (tomorrow) at RubyNation, which is already well underway (I’m missing the first day, though). My topic is “Seven Things To Know About MySQL Performance.”
Further Reading:
While working through prepared statements in MySQL, there was an interesting MySQL regular expression question raised. A student wanted to know how to address the following error message:
ERROR 1139 (42000): Got error 'repetition-operator operand invalid' FROM REGEXP
They had substituted * for a .+ in a
metasequence. A metasequence is a parenthetical expression that
evaluates based on multiple alternative conditions, and the pipe
(|) acts as an OR operator. The full
code example is found on page 482 of the Oracle Database 11g & MySQL 5.6 Developer
Handbook. The student’s change would have worked without an …
I guest-posted on Fusion-io’s blog about the database’s working set size and the interplay with fast Flash storage. It’s written from a MySQL point of view, but it’s applicable to many types of systems.
Further Reading: