Answering questions asked on the site.

Victor asks:

I have a table which I will call sale to protect the innocent:

I need to retrieve ultimate values of price, amount and date for each product:

SELECT  product,
MIN(price), MAX(price),
MIN(amount), MAX(amount),
MIN(date), MAX(date)
FROM    sale
GROUP BY
product

The query only returns about 100 records.

I have all these fields indexed (together with product), but this still produces a full table scan over 3,000,000 records.

How do I speed up this query?

To retrieve the ultimate values of the fields, MySQL would just need to make a loose …

Welcome to this week's (slightly late) edition of Last Week in Drizzle.  This week sees the kick-off of many new features for the next release of Drizzle codenamed 'Fremont' and the mailing list is a hive of activity around Google Summer of Code.  I apologise for publishing a few days late this week and will try and stay on-track for future editions.
Fremont
In the tradition of Drizzle using Seattle road names in alphabetical order for codenames the next release of Drizzle is codenamed 'Fremont' (the current GA release is codenamed 'Elliott').  Monty Taylor has outlined the merge process going forward as can be seen in his mailing list post.
Google Summer of Code
We have been accepted for …

A couple of weeks ago at the San Francisco MySQL Meetup, I gave a talk on HandlerSocket and got a couple of questions that, while I thought I knew the answer, I had never actually verified by testing. So, for the attendees who asked, here are the questions and answers: Can you use HandlerSocket on [...]

Welcome to this week’s (slightly late) edition of Last Week in Drizzle.  This week sees the kick-off of many new features for the next release of Drizzle codenamed ‘Fremont’ and the mailing list is a hive of activity around Google Summer of Code.  I apologise for publishing a few days late this week and will try and stay on-track for future editions.

Fremont

In the tradition of Drizzle using Seattle road names in alphabetical order for codenames the next release of Drizzle is codenamed ‘Fremont’ (the current GA release is codenamed ‘Elliott’).  Monty Taylor has outlined the merge process going forward as can be seen in his mailing list post.

Google Summer of Code

We …

I am happy to announce that Percona Server and Percona XtraBackup are now available for the Solaris platform!

• You can download the latest Percona Server 5.1.55-12.6 stable release from our download area.
• As for Percona XtraBackup, we made Solaris binaries for the upcoming XtraBackup-1.6 release, and they are available from “pre-release” directory.

There are couple features not available in Solaris build of Percona Server: Response Time Distribution (it depends on GCC platform specific functions) , HandlerSocket ( compilation issues on Solaris), Maatkit UDFs.

I want to say a special thanks to Joyent (a cloud computing provider) for Solaris resources and …

How old is your timezone info?
As the timezone database gets updated frequently, it's important to update the internal timezone database in MySQL. This is needed for the CONVERT_TZ() function for work properly.

It's not easy to determine if the data in mysql is older than the data from the tzdata database. It get's a bit better if you use this hack before importing the zone database:
ALTER TABLE mysql.time_zone_transition ADD COLUMN `Last_Update` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;I've created Bug #60675 to get the system database updated.

Which database serves still need timezone info?
Use …

I feel that MySQL has lost its coolness factor.
Why do I think that? Well, because most people are interested in other things nowadays...
Other things like NoSQL - It's what all the cool kids are talking about.

NoSQL is as cool as it is partly because MySQL wasn't performing as fast as some people would have liked and eventually they looked for other alternatives.
Meaning that MySQL was the default database when people were installing their PHP/Ruby/Java/Python...etc website, but after some time, it got slow and difficult to maintain.



Difficult to Maintain (or not Web scale)

Lets try to analyse what "difficult to maintain" really means...
It could mean any, some or all of the following situations:
Your website got bigger -

1. Immediately (flood of …

This post is about one idea for GSoC 2011 regarding openSUSE Connect. I already wrote about it some time ago, but now is time to elaborate a little bit more.

First of all, let me state, that I already found a qualified student, that wants to work on this idea and that has also some good suggestions. So I’m not searching for a student with this post, but I want to share with you the goals of this project and why I think it is important.

Let’s start again with what it is all about. We as a openSUSE Project have many contributors. People provide not only code, but they write documentation on our wiki, report bugs, organize release parties, organize booths at conferences and much more. Obviously we should make their effort recognized …

For those who didn't know it already: MySQL.com was "hacked" last weekend. There also was a report about SQL injection on 2 Sun.com domains.

I haven't seen an announcement from Oracle yet.

More info on:

• http://seclists.org/fulldisclosure/2011/Mar/309
• http://tinkode27.baywords.com/sun-com-sun-mycrosystems-vulnerable-sql-injection/

Have you ever seen BIG weird numbers in mk-query-digest report that just seem wrong? I have! Here’s one report I got today:

...
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time          5088s     1us    171s     2ms   467us   104ms    28us
# Lock time            76s       0      3s    26us    69us     3ms       0
# Rows sent          9.80M       0   1.05M    3.50    0.99  642.32       0
# Rows examine       5.59G       0  82.56M   2.00k    0.99  97.41k       0
# Rows affecte     457.30k       0   2.62k    0.16    0.99    1.68       0
# Rows read          2.16G       0  82.56M  788.53   21.45  82.91k    0.99
# Bytes sent         2.14T       0   4.00G 781.27k   3.52k  47.84M   84.10
# Merge passes     273.47G       0   4.00G  97.69k       0  10.35M       0
# Tmp tables …