If you’ve ever created foreign keys on an InnoDB table, you’ll see it automatically creates indexes, if none exists, on the referenced columns in the parent table, and also in the foreign key columns in the child table. This article explains why both are needed. Why index the parent table? When a row is inserted or updated in the child table, the parent table must be searched for a row whose referenced values match the values in the foreign key columns.

So, my previous entry which contained a performance tip regarding the use of prepared statements got a few good comments, and prompted me to write a follow-up post summarizing the various comments and expanding a bit on the problems with using prepared statements.

What Are Prepared Statements, Anyway?

OK, so the concept of prepared statements have been around for quite some time. The basic advantages of prepared statements (from a conceptual point of view, not necessarily a realistic point of view for MySQL) are that SQL statement parameters — in other words, the WHERE, INSERT or UPDATE SET constants supplied to the SQL statement — can be represented by a token, such as "?", and at run-time, the tokens are replaced with actual parameters after being escaped. The escaping of the variables is important to mitigate the threat of certain security attacks, such as SQL injection. Additionally, in theory, the prepared …

Here are the slides from my OSCON 2006 talk about MySQL Cluster and how MySQL turned it from closed source into open source. Enjoy.

Lars Thalmann: Becoming Open Source

OSCON 2006 workshop

Building “Sane” Query Interfaces, by Mark Newsome e-mail is newsome@acm.org

Insane query interfaces have no help, no format, no range, and no help on how many results will be returned.

Better — pull-downs with a descriptive default like “select state”. Find min and max date from db. Gives a count for how many items in the pulldown menu. UI automatically reverts to use a popup button when pulldown gets too long.

“Refine” button can be pressed when the form is filled, and it will get a count, so you can “preview” your results. When the “refine” button is pressed, it refines EVERYTHING — ie, the counts for the range for the other items. Very smart! (see the song database demo below, when you enter in a lyrics search and click refine, it repopulates the pulldown menus with only the artists and titles that have the lyrics you requested). Also, if there’s …

There are 5 types of table partitions in mysql: range, list, hash, key and composite (or subpartitioning), i think the most commonly used type in the database world (at least in oracle) could be range partition, in this way a expression is evaluated and according to it different partitions are created with ranges of rows. The most common method to partition by range is using dates (by years,months,days) to group large amounts of rows from a table, mysql does not support partition by date type directly (oracle does), instead a function like year() or month() should be used to get an integer value.


Playing with this partition type, first i tried to create a table with a primary key on the id column and the partition column using insert_date, but MySQL generates an error if the column insert_date is not part of the primary key:

mysql> CREATE TABLE range_partition_tab (
 ->  id numeric …


CREATE TABLE `test_unique` (
`id` int(10) unsigned NOT NULL auto_increment,
`col` varchar(5) default NULL,
`col2` varchar(50) default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `col` (`col`)
);


SELECT * FROM test_unique;
+----+------+------+
| id | col | col2 |
+----+------+------+
| 1 | | blah |
| 2 | | blah |
| 3 | | blah |
| 4 | blah | NULL |
+----+------+------+

UPDATE test_unique SET col=\N WHERE col='';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0

SELECT * FROM test_unique;
+----+------+------+
| id | col | col2 |
+----+------+------+
| 1 | NULL | blah |
| 2 | NULL | blah |
| 3 | NULL | blah |
| 4 | blah | NULL |
+----+------+------+
4 rows in set (0.00 sec)

…

Less than an hour until my OSCON talk on how we moved from closed to open source with MySQL Cluster.  Will be fun to talk about this.  If you are at OSCON, feel welcome to come and listen.  Hopefully there will be a little time for questions too.  Room E143-144 at 11:35…  See you

 Btw, tomorrow I give a more technical talk about MySQL Replication.  You are most welcome then too.

Consider this:

You create a piece of code that is truly valuable for MySQL. We give you a T-shirt. Does this feel right?

Of course it doesn’t. The CLA (Contributor License Agreement) just contains the T-shirt as a default so-called consideration and a token of appreciation. Several people rightly reacted to the imbalance of T-shirts versus valuable code in my original CLA post.

The legal concept of “consideration” in American or English law means more-or-less compensation, i.e. a countercommitment to make the contract not just balanced but enforceable. Wikipedia’s definition of consideration starts like this:

Consideration is defined as a bargain for …

At the MySQL reception last night at OSCON, which was an astounding success (> 140 people I believe), I ran into one of the LiveJournal crew who told me about a success story they have had with one of the pluggable storage engines that is usually skimmed over and rarely discussed: the ARCHIVE storage engine. According to the LiveJournal employee (I left his business card back at the hotel so I can't remember his name...) they switched from using MyISAM to ARCHIVE for their Apache server logging and noticed a 400% performance improvement (from a disk I/O perspective) and at least a 20% reduction in storage size.

This little story highlights the main point I try to get across during my tutorials and presentations on performance tuning: that you should take advantage of the MySQL storage engine architecture by using the storage engine best suited for the job. In this case, using the …

Hi all -

As many of you know, we have a new backup/restore project underway. I’ve just posted the first draft of the functional spec on mysql forge, and would love to get as many eyes on it as possible. Please take a look when you can and shoot me any feedback at rschumacher@mysql.com

Thanks!