mysql

"Hello. I'm Ryan Mack, a new member of the Facebook MySQL team. My first order of business is evaluating MySQL 5.1 and the new InnoDB plugin. So far things look very promising, but I came across one issue worth sharing." - Arnaldo M Pereira from Bookmarklet

"While learning a new ORDER BY syntax recently, as a diligent architect/DBA I reviewed the documentation. What I also found in the SELECT syntax which I did not also know was the keyword DUMPFILE. The SELECT Syntax from MySQL 5.1 Manual states: If you use INTO DUMPFILE instead of INTO OUTFILE, MySQL writes only one row into the file, without any column or line termination and without performing any escape processing. This is useful if you want to store a BLOB value in a file. It’s a shame there is no middle ground, where you get the features of OUTFILE (i.e. all rows), and the features of DUMPFILE (i.e. no heading)" - Arnaldo M Pereira from Bookmarklet

"You probably know that it’s possible to set configuration variables to log queries that don’t use indexes to the slow query log in MySQL. This is a good way to find tables that might need indexes. But what if the slow query log isn’t enabled and you are using (or consulting on) MySQL 5.0 or earlier, where it can’t be enabled on the fly unless you’re using a patched server such as Percona’s enhanced builds? You can still capture these queries." - Arnaldo M Pereira from Bookmarklet

"Why must SELECT COUNT(*) FROM FOO run fast? It is much more valuable to make that query fast when it has a WHERE clause. When there isn't a where clause, MyISAM executes SELECT COUNT(*) FROM FOO fast. When there is a WHERE clause, MySQL has limited support for combining index scans but nothing like bitmap indexes." - Arnaldo M Pereira from Bookmarklet

"Percona XtraDB is an enhanced version of the InnoDB storage engine, designed to better scale on modern hardware, and including a variety of other features useful in high performance environments. It is fully backwards compatible, and so can be used as a drop-in replacement for standard InnoDB. " - imabonehead from Bookmarklet

"Percona XtraDB includes all of InnoDB's robust, reliable ACID-compliant design and advanced MVCC architecture, and builds on that solid foundation with more features, more tunability, more metrics, and more scalability. In particular, it is designed to scale better on many cores, to use memory more efficiently, and to be more convenient and useful. The new features are especially... more... - imabonehead

"I remember meeting Giuseppe Maxia (aka The Data Charmer), probably the most famous Italian MySQL enthusiast and hacker, back at what must have been the first MySQL Conference. At the time I’d seen him on mailing lists and knew he gave talks from time to time, but I had no idea of the useful software he’d be creating. This week, I’d like give you a tour of one of his projects: MySQL Sandbox. I think of it as a sort of virtual server manager for MySQL instances. In fact, the one-line intro on the project web site hints at that: Quick painless install of side MySQL server in isolation. MySQL Sandbox is a tool for installing one or more MySQL servers in isolation, without affecting other servers. Isolation tends to be the hardest thing to get right (aside from the automation itself) when trying to bring up multiple MySQL instances on a single machine. There are a lot of hard-coded default paths (and port numbers) that will cause you headaches unless your track down all the references and... more... - Arnaldo M Pereira from Bookmarklet

"I wasn’t sure I was to go this far. After catching breath the following have been added to Generic, auto scaling, scaled SQL graphs, and these will conclude my current hacks:" - Arnaldo M Pereira from Bookmarklet

Is this sick, or what? - Arnaldo M Pereira

"We all love graphs. We all love SQL hacks. We all know the SQL hack which displays a character-based graph (example follows for those unfamiliar). But we all love horizontal graphs, not vertical ones. We are used to the X axis being horizontal, Y being vertical. Not vice versa. In this post I’ll present a SQL hack which rotates a vertical graph to horizontal. In fact, the technique shown will rotate any ‘textual image’; but graphs are a nice example." - Arnaldo M Pereira from Bookmarklet

Very cool, one should check the article out. I don't think it has practical uses, but it's just cool. - Arnaldo M Pereira

"A team of researchers will release on Tuesday a paper showing that parallel SQL databases perform up to 6.5 times faster than Google Inc.'s MapReduce data-crunching technology. Google bypassed parallel databases and invented MapReduce as a way to index the World Wide Web on its global grid of low-end PC servers. As of January 2008, Google has used MapReduce to process 20 petabytes of data a day. In results of in-house tests published last November, Google used MapReduce running on 1,000 servers to sort 1TB of data in just 68 seconds. Such results have won MapReduce and its open-source version Hadoop many fans, who argue that the technology is already superior to the 40-year-old relational one for large-scale grids such as for cloud-computing infrastructures, and will eventually render databases obsolete for other tasks." - Arnaldo M Pereira from Bookmarklet

"Over a chat on the #workbench IRC channel, Collin Cusce has written a handy little Lua script to automatically create relationships (through foreign keys) for his reverse engineered database. Reverse engineering the DB to import tables into a diagram was easy, but their database used no “hard” foreign keys and an ER diagram without relationships wouldn’t be of much use. So one option would be to individually connect each foreign key column pair by hand, using the relationship picking tool . But doing that for the thirty-something tables in the database would be too much work and something could be overlooked and left out. The other option would be to automate that, since all such foreign keys followed a naming convention like <table>_id<column> or fk_id<table>. And that led to the following (slightly modified) script that will do just that." - Arnaldo M Pereira from Bookmarklet

"For the last weeks, I have been writing a tool called "Incline," a program that automatically maintains consistency between sharded MySQL databases. The aim of the software is to free application developers from hand-writing code for keeping consistency between RDB nodes, so that they can concentrate on writing the application logic." - Arnaldo M Pereira from Bookmarklet

"Recently new performance builds were made by our MySQL Team, and it seems to me reaching now a new milestone with the build #46 - within this single build Mikael joined several previously tested performance builds showed a positive performance improvement. So, how well it's comparing now to the default 5.4 ?" - Arnaldo M Pereira from Bookmarklet

"What's the Librarian? It's a place where everyone posts links to interesting material concerning MySQL. You can add links and descriptions to presentations, pictures, videos, articles, and books. You can tag the posts you added and add or edit tags to existing posts, to put such pieces in relation to other material. Thus, using tags, you will be able to identify a presentation on partitioning at the UC2009, and all the blog posts that talked about that topic during the conference or after." - Arnaldo M Pereira from Bookmarklet

"This question “what problems will I have when migrating to the cloud” gets asked often enough. If by cloud you mean Amazon EC2, then from a technical perspective there isn’t much that changes. The biggest thing that changes is just how you pay your bill." - Arnaldo M Pereira from Bookmarklet

"When MySQL Workbench was still in alpha and beta stages, some dark murmurs in the community suggested that the OSS (free) version was crippleware, part of an evil plot to make you pony up $99 if you wanted to get any real benefit. Closed source and profits and bears, oh my! The new GA release of Workbench gets some of those “crippled” features back. In particular, the OSS version now allows connections to a live server, so you can reverse and forward engineer without having to go through a dump file. I also have it on good authority that the OSS version will be able to print diagrams directly, instead of you having to export an image. If you see any merit in a visual diagram of your database, but you wrote off Workbench based on earlier experiences, give it another try now." - Arnaldo M Pereira from Bookmarklet

Topics covered: How to use the PHP server-side scripting language and the MySQL database engine to underlie dynamic Web sites (those that rely on database queries) and full-on Web applications, such as those that require session management and maintenance of user rosters. Tutorials in both subjects begin with the basics and proceed through moderately complicated stuff, though there's no absolutely comprehensive reference here. --This text refers to the Paperback edition. - New Tech Books

Hi Steve, if you've been for an Open Query one day course, do you have some feedback about it? Tell us what worked for you, and what didn't. Its also interesting to see that Arjen has created OQ newsletters - kudos! - Colin Charles from twhirl