mysql> CREATE TABLE innodb_string_test (h varchar(512) ) TYPE=INNODB;
Query OK, 0 rows affected, 2 warnings (0.55 sec)

mysql> show warnings;
+---------+------+--------------------------------------------------------------------------+
| Level   | Code | Message                                                                  |
+---------+------+--------------------------------------------------------------------------+
| Warning | 1246 | Converting column 'h' from CHAR to TEXT                                  |
| Warning | 1287 | 'TYPE=storage_engine' is deprecated; use 'ENGINE=storage_engine' instead |
+---------+------+--------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Technically innodb supports defining varchar's greater then 255 characters (utf8 are double bytes so don't assume that 255 characters mean bytes-FYI). …

It's hard to believe I'm back. It's so quiet here.

Saturday morning, it took too long for the hotel clerks to check us out. They'd screwed up my credit card, krow's credit card, messed up their room reservation log, and so forth. And we needed at least the receipt for krow's corporate card before we left. (I let them handle mine off-line. If they screw it up, I just dispute the charge and wash my hands.)

The session about non-relational databases was interesting. The guy from Twitter talked about their queue db. They are using the memcached wire protocol to talk to it! Dabble talked about their neat little cheat, where they don't actually have a …

Just wanted to say thanks to the many of you that responded to my recent MySQL on Windows article that was posted two weeks ago. Without a doubt, this article has caused more feedback than any other piece I’ve written for our Dev/Community zone.

If haven’t read the article yet and you’re using MySQL on Windows now, or you’ve been thinking about it, please check it out and shoot me your thought on MySQL for WIN (what you like, hate, how you’d make it better, etc.)

Thanks!

Because I’m busy today, here’s what the announcement looks like (kudos to my colleagues Larry and Daniel for coming up with the following):

MySQL AB is very grateful for our very large Community, and it is important to us to meet and support our users! So we are happy to announce that Colin Charles, MySQL Community Relations Manager, APAC, will be visiting the following locations to meet with MySQL users.

Colin will be prepared to discuss the following with you:

• How to start your own user group
• Review of technical questions and issues
• The MySQL product roadmap and future directions
• MySQL product architecture

You can meet with Colin and speak to him one-on-one during following dates:

• Kuala Lumpur / June 27- July 12, 2007
• Singapore / July 4 - 6th, 2007
• Tokyo / September 11-12th, 2007
• Beijing / September …

Did you know that MySQL supports:

DELETE FROM t1 ORDER BY a;
?

MySQL manual says:
If the ORDER BY clause is specified, the rows are deleted in the order that is specified.

You can use stored functions and subselects there too if you like.

I should start using this feature immediately.

Samsung has announced a new line of 64G SSDs for use in consumer laptops. Nice to see innovation but I REALLY want to see more SSDs in the server space.

I JUST want to use a CHEAP 2-5G SSD for write ahead and binary logs. I just need 1k write ops per second with a SATA interface. Then I could drop them in cheap commodity servers and get a significant performance boost. My disks would still use software RAID 0 (at least until I replace this partitioning) but only store data.

Give me one of these for $50 to $100 and I’ll buy one with ever server.

The fsync’s required for writing to disks on INNODB can really hurt performance. My bet …

Well, it seems many have put up their wishlist for features they would like to see in MySQL so here is my list. These are in no particular order of importance, just as they came to mind :)

1. Online Backup - This is currently being developed and it will make a big difference to many people currently trying to administrate MySQL. We can only hope that it gets done soon and is well tested to avoid any major issues.

2. Standardised HA/Replication - Along similar lines to the consistent online backup is that of consistent high availability, load balancing and replication. No matter what the engine being used, this will allow a consistent method of implementing these features regardless.

read more

Well, it seems many have put up their wishlist for features they would like to see in MySQL so here is my list. These are in no particular order of importance, just as they came to mind :)

1. Online Backup - This is currently being developed and it will make a big difference to many people currently trying to administrate MySQL. We can only hope that it gets done soon and is well tested to avoid any major issues.

2. Standardised HA/Replication - Along similar lines to the consistent online backup is that of consistent high availability, load balancing and replication. No matter what the engine being used, this will allow a consistent method of implementing these features regardless.

read more

I am indeed amazed when I consider how weak my mind is and how prone to error --Rene Descartes

This is not a recommended approach; only explained here so that you can identify it when it presents itself and avoid it.

The following indexes are created on the star schema example shown before:

1. Concatenated index is created on the fact table: sales_transaction (transaction_detail_key, product_key, sales_org_key, calendar_key) - note the difference, compared to the nested join approach where multiple indexes on each foreign key were created
   
2. In addition some indexes may be created on the dimension attributes that are frequently queried: transaction_details (transaction_type), product (SKU), sales_organization …

One cannot have everything the way he would like it --Mark Twain

In the absence of other advanced techniques for star join optimization, nested fact join with dimensions while ensuring that the indexes are used for each dimension join delivers reasonable performance.

The following indexes are created on the star schema example shown:

1. Primary key unique index on surrogate key for each dimension. One index each is created on: calendar (calendar_key), transaction_details (transaction_detail_key), product (product_key), sales_organization (sales_org_key)
2. One index is created for each foreign key on the fact table: sales_transaction (calendar_key), sales_transaction (transaction_detail_key), sales_transaction (product_key), sales_organization (sales_org_key)
3. In addition some indexes may be …