Hi all -

Just a quick note to let you know that I’ve posted a new article on how to use MySQL 5.1 partitioning with date-based partition keys. Mikael Ronstrom already put up a nice blog post on this, and I figured I’d follow up with an article on the same subject since we’ve had a number of inquires.

Thanks (as always) for supporting MySQL!

I recently had to help a friend go through a site and correct alot of wrong URLs. Here is an easy way to do search and replace in MySQL, for those of you who didn’t know.

If you have phpMyAdmin you can use the SQL-tab to run this query. If not, you can run it from the commandline as well. Here is the query I ran: (remember of course to replace table_name, column_name, find_this and replace_with_this with real data)

UPDATE table_name SET column_name = replace(column_name,"find_this","replace_with_this");

[tags]MySQL,databases,sql,query[/tags]

Someone at work pointed out a rather interesting blog entry that purports to dump on MySQL Partitioning in a rather big way.In my opinion, he raises one valid point, but as for the rest of what he says, he's either misguided or misinformed. Except where he's Just Plain Wrong. Why? Let me give some reasons... He seems to be confusing Partitioning with Cluster. NDB Cluster does use partitioning (and has done so since before it was part of MySQL), but in 5.1 it's possible to use partitioning with most other storage engines, no Cluster necessary. (This is what's actually "new" about it in 5.1.) Whether he's talking about Cluster or about Partitioning (it's not clear which is the case), his contention that every box ... contains the whole database is simply wrong. He is correct in saying that you can't add or drop Cluster nodes online. This is being worked on. I can't say whether it'll make it into 5.2 (I personally doubt it, but I could be wrong about …

Someone at work pointed out a rather interesting blog entry that purports to dump on MySQL Partitioning in a rather big way.In my opinion, he raises one valid point, but as for the rest of what he says, he's either misguided or misinformed. Except where he's Just Plain Wrong. Why? Let me give some reasons... He seems to be confusing Partitioning with Cluster. NDB Cluster does use partitioning (and has done so since before it was part of MySQL), but in 5.1 it's possible to use partitioning with most other storage engines, no Cluster necessary. (This is what's actually "new" about it in 5.1.) Whether he's talking about Cluster or about Partitioning (it's not clear which is the case), his contention that every box ... contains the whole database is simply wrong. He is correct in saying that you can't add or drop Cluster nodes online. This is being worked on. I can't say whether it'll make it into 5.2 (I personally doubt it, but I could be wrong about …

One nice feature added for EXPLAIN statement in MySQL 4.1 is EXTENDED keyword which provides you with some helpful additional information on query optimization. It should be used together with SHOW WARNINGS to get information about how query looks after transformation as well as what other notes optimizer may wish to tell us. It is best seen by example:

PLAIN TEXT SQL:

1. mysql> EXPLAIN extended SELECT * FROM sbtest WHERE id>5 AND id>6 AND c="a" AND pad=c;
2. +----+-------------+--------+-------+---------------+---------+---------+------+--------+-------------+
3. | id | select_type | TABLE  | type  | possible_keys | KEY     | key_len | ref  | rows   | Extra       |
4. +----+-------------+--------+-------+---------------+---------+---------+------+--------+-------------+
5. |  1 …

Running EXPLAIN for problematic queries is very powerful tool for MySQL Performance optimization. If you've been using this tool a lot you probably noticed it is not always provide adequate information. Here is list of things you may wish to watch out.

EXPLAIN can be wrong - this does not happen very often but it does. EXPLAIN may not be telling you the truth, or full truth about query execution. The reason is - MySQL does not really have the special "plan generating" step, instead plan is generated as a part of query execution, sometimes being dynamic based on data. With EXPLAIN MySQL kind of simulates execution but it obviously does not access the data so does not have access to this dynamic component. If you suspect EXPLAIN is lieing you you can use SHOW STATUS "Handler" statistics to see if number of operations match.

EXPLAIN works for SELECT only This is in works to be fixed …

It's very important for me to keep closely in touch with MySQL's products and all the fun stuff that comes with working with databases which is also a good way to keep in memory what the background of my job and our company is. A job is much more fun if you know and use the products that the company you work for produces instead of only doing it to get paid (that's something I also know well from local companies I worked for earlier - that makes a huge difference). Because of this, I never want to lose the fun side that comes with Community activities which involves to sometimes simply play and experiment with various things that come to my mind and write about that.

Unfortunately I found a little less time recently for Community related activities than the months before, but there are plans which I hope will bring that back to what it used to be. During the next months I plan to reorganize my working environment and my PC …

In my ongoing quest obsession to find a decent handheld computer that you can type on, I was intrigued by the latest announcement from Sony for their new Vaio UX180P Micro PC. That is, until I tried it out.  The Vaio UX has all the right specs on paper: it weighs just 1.2 pounds, has a 4.5" Super VGA screen with 1024 x 600 resolution, a 1.2 GHZ Intel Solo Core CPU, a 30 gig hard drive, built-in wifi, bluetooth and Cingular EDGE wireless access, a biometric fingerprint reader (no joke!), USB port, headphone / microphone jacks, two --yes, two!-- digital cameras front and back, a slide-out keyboard and an optional docking station.  It even …

Get it here.

Changes:

• Now indexing misspelled word versions for fast lookup
• Indexed search for partial words
• Add “:” to break characters
• Check minimum_length and stopword list when adding to index, to keep it smaller
• Add clear_data() procedure
• Increase points given to link target when both source and target are in the search results
• Give points for query keywords making up a high percentage of content (”foo bar zig” wins over “foo bar zig zag” in a search for “foo bar”)
• Give points for keywords earlier in the query (”zig” wins over “zag” in a search for “zig zag”)
• Add unit tests

I've just improved innotop substantially, and released version 0.1.106 (download innotop from the original article), and I'm also preparing a series of articles on how to use it for real, practical things. I'd like to know what you think of it, what problems you have, what features you want. It would be a huge help if you'd start it, toggle through its modes, and give me your feedback.