The latest from the MySQL community

A

Since few days I was debugging a case from Yahoo! Japan team (thanks to Akira Kitada) where the query runs fine in about 0 secs with the 5.1.16 build where as the same query takes almost close to 2 minutes with the latest 5.1.22 release.

One can simply identify that this is a optimizer issue as it is not picking the right index. Lets consider the following two tables urls and urls_categories…

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

| urls  | CREATE TABLE `urls` (
  `url_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `url` VARBINARY(2000) NOT NULL,
  `url_status` …

… I didn’t get two-way sync done, and I didn’t get the Nibble algorithm done. That much I expected. But I also didn’t get the current work released tonight because I’m paranoid about breaking things. I’m trying to go through all the tools and write at least a basic test for them to be sure they can do the simplest “unit of work” (such as mk-find running and printing out that it finds the mysql.

This is the last day I’m taking off work to hack on mk-table-sync, and I thought it was time for (yet another) progress report. Here’s what I have done so far: All the code, except for a tiny bit of “glue” and “setup” code, is in modules. Lots more tests for the modules. A new sync algorithm (I still haven’t rewritten the top-down and bottom-up, which are designed for network efficiency more than MySQL efficiency, and are very complicated).

Saw this on the Association for Computer Machinery SIGMOD announcements.

Tribute to Honor Jim Gray

It is scheduled for May 31, 2008 at UC-Berkeley.

As a follow-up to my earlier blog, I happened to get 15 minutes hands-on use of the Amazon Kindle, their new portable eBook reader. As it happens, Amazon's Lab 126 development team is located in the same building as MySQL's US headquarters, and so when I happened to spot a team of folks with a Kindle in hand at a local restaurant for lunch, I dove in and asked if I could try it out. First point to note: the display is fantastic. To me, it's as easy to read as a book. While you can argue that the text... READ MORE

My saga with RSS continues. As I stated before, changes to MySQL's forum site led me to pursue using an RSS reader as my main interface to the forums. That worked quite well until recently. My only issue had been that the various replies to a post showed as separate entries to the reader, with no connection between them. Coming back from a self-imposed hiatus, I find that this problem has been fixed, at the expense of creating another one. Now, each thread shows up once only in the reader, along with an indication of how many replies have been posted. This is fine, except that the thread is not marked as updated, and so threads that get replied to are never highlighted as unread by the reader. This of course makes it extremely difficult to conduct timely and efficient interactions with someone on the board. I now have to peruse the forum web page with the thread …

This is the configuration file of MySQL. For new DBAs, depending on your needs (eg. if the database is small, medium, large), just copy the appropriate my.cnf in /usr/local/mysql/support-files/ . Then just change couple of parameters and you are all set. There is no need to make a huge fuss about it. Many experts will tell you to change this file, I recommend not to listen to them. MySQL was created to be simple... If you really need to change it, then make sure you know what you are doing.

Our Services team do regular surveys on our Support customers. I just had a look at the latest data from proactive calls during July to November 2007. In the sample, the Platinum, Gold, Silver and Basic levels were roughly equally big slices, with a bit fewer Gold and a bit more Basic.

The figures are quite interesting. One out of two top conclusions is that we seem to need some more evangelising among our customers, especially for MySQL Enterprise Monitor and for the MySQL Enterprise Knowledge Base.

• Between half and two-thirds of the surveyed customers have heard of Monitoring; the higher the SLA level, the more.
• The Knowledge Base has more penetration than …

In the last article the performance impact of joins was shown.  This one will demonstrate cases where denormalized joins are a bit faster, as will the third article with larger data volumes.  The fourth article, the most interesting one, will show where a denormalized data model can be 50 times faster than a normalized data model. 

Here are the tables that will be involved in the sql.  The normalized ProductSmall table has a 100 million rows and is about 0.67 gig. 

create table ProductSmall (

    productSmallId int(11) not null,

    productSmallName varchar(32) not null,

    productGroupId int(11) not null,

    primary key (productSmallId),

    key idx_ProductSmall_productGroup (productGroupId),

    constraint con_ProductSmall_productGroup