MySQL performance is largely defined by keys and how efficiently queries can use them. As you scale, at certain point it isn’t enough anymore to just have any indexes and still get a good performance in return. You have to really figure them out and allow your queries to do less work, as little work as possible.

The approach presented in this article can sometimes help designing such good, efficient indexes. As a consultant, I have to rely on it myself from time to time, having to optimize a query that works in a database I know nothing about.

Let’s assume there is an application, which collects user activity in various places. The application uses a poorly indexed database, so there are plenty of examples to choose from. Our example query performs a full table scan, which means it reads all rows from the table it uses. It is also among the most popular statements executed by application.

mysql> EXPLAIN …

We are pleased to announce the immediate availability of MariaDB 5.1.62 and MariaDB 5.2.12. Both of these stable (GA) releases incorporate MySQL 5.1.62 and several bug fixes.

Most importantly, MariaDB 5.1.62 and 5.2.12 include a fix for a bug that under certain rare circumstances allowed a user to connect with an invalid password. This is a serious security issue. We recommend upgrading from older versions as soon as possible.

Please see the What is MariaDB 5.1 page for an overview of MariaDB 5.1, and the What is MariaDB …

The purpose of technology is the make the life easier and more quality oriented. It is this virtue of technology which makes it evergreen and sustainable. The unique feature of technology is its innovative nature. Technology blogging is a way for the technologists to rant about technology and throw light at known and unknown corners [...]

PC World has written a post with this title(*) about the upcoming MySQL Connect conference and references the Percona Live conference and an official Percona comment. As this is not syndicated in Planet MySQL I encourage you to read the full article.

This is the MySQL conference to get technical presentations by the many great Oracle/MySQL technical staff who will not be in attendance at Percona Live. There will also be a strong community presence in speaking at Oracle Connect in September. While Oracle was organizing a dedicated MySQL event in April for the community with all vendors including Percona to replace the conference dropped by long term partner O’Reilly (kudos …

Many people are aware that MySQL 5.5 added support for external authentication plugins, and that Oracle provides several commercial-licensed plugins that can help users leverage this functionality out-of-the-box (you can try these and other features of MySQL commercial offerings for free).  Until the recent release of Connector/J 5.1.19, though, JDBC users could not leverage the plugin capabilities of MySQL 5.5.  Now, Java users can write their own client-side plugins in support of the standard MySQL 5.5 external authentication plugins, or even server-side external authentication plugins they write themselves.

…

Indexes are tricky things. In my experience, indexes are added whenever SQL queries are too slow. This makes sense. However, sometimes these indexes were added without being thought through enough (I am guilty of this). Sometimes they were thought through, but the table itself now has so many indexes that the optimizer doesn't know which one to choose from. 
The overhead of indexes greatly effects tables that need to be written to or altered often (even indexes on NoSQL database greatly effect writes).
Over indexed tables inflate the database size dramatically which adds to people's concerns that the database becomes unwieldy.
Read more »

A very useful helper in your join toolbox can be a delete join. Even though it’s not a special join type but a join used within DELETE statements, it’s still worth mentioning. However, from time to time when I want to make use of delete joins  on my own, I somehow managed it to forgot the syntax and have to look it up somewhere. Therefor, here is a description as well as an example.

Take care: A delete join is a very powerful weapon. And with great power comes great responsibility! I recommend to develop the delete join on a development database. At least, make sure you have a working an recent backup before trying to delete things. A delete statement that uses joins makes it easy to shot yourself in the foot. And if you do, it probably blows away your hole leg.

DELETE join syntax

When you …

Great speaker line up at SkySQL & MariaDB: Solutions Day for the MySQL Database and Percona Live MySQL Conference & Expo

If you haven’t yet registeredfor next week’sSkySQL & MariaDB: Solutions Day for the MySQL Database, do it today so you don’t miss any of these important discussions about MariaDB, MySQL & related technologies:

• Opening keynote from Monty Widenius and David Axmark

• SkySQL, MySQL, MariaDB & the Cloud, withUlf Sandberg, Kaj Arnö, and Ivan Zoratti of SkySQL

• Automating master failover and non-stop master switch …

I will be attending the MySQL Innovation Day.  Don't just listen to a speech, come and interact with the engineers and the MySQL community!

The event will be featuring multiple “lightning” talks, BOFs with the Oracle’s MySQL Engineers.  If you cannot attend please join us via a live webcast.Some members of the MySQL community will also be invited to give presentations.

This event is being hosted at Oracle’s Conference Center in Redwood Shores, CA on June 5th, 2012.

Register information will be coming soon! This will be a great opportunity to preview some of the content that will be delivered during MySQL Connect Conference in September.

The MySQL Connect Conference call for papers will open on April 16, 2012 for approximately three weeks. We are looking for those of you in the community who want to present on what you are doing with MySQL in the areas of Performance and Scalability, High Availability, Cloud Computing, Architecture and Design, Database Administration, and Application Development. This will be an amazing show and we want you to be a part of it — so write up a session description and you have until the 16th to make it shine!

…