Following question came up recently: What happens if you drop or add a partition of an existing already partitioned table with ALTER TABLE? Will it be copied or will just the single partition be dropped and added? How are the index(es) rebuild after such a drop/add?

I have to disagree with the hard-won wisdom Joseph documents in this blog entry. Altough I 100% support his idea of version control for config files. Both his "Use mapfiles" and "Avoid generating configuration files" are 2 practices that I have to disagree with.

Don't use map files, use actual subdirs and map multiple trees on top of eachother, Create trees such as ALL , HOSTNAME , HOSTNAME-X , etc and layer them on top of eachother for the final filesystem. ALL is written first, then hostname and finally hostname-x, so if your CLUSTERNODE13 has a specific config file /etc/murphy that differs from all other nodes it sits in overrides/CLUSTERNODE13/etc/murphy. In the original sugestion with map files.. how would you map different content based on a server to the same filenames ? You'd end up getting a big bunch of …

This is pretty simple approach I often use called to optimize web application performance if problem happens with few pages. If we have "everything is slow" problem looking at slow query logs may be better start.

So what could you do ?

Look at the information shown on the page which comes from database. This is important as this is information you need to have retrieved from database one way or another if you want to keep page look the same. Your task is simply to have this information delivered from the database most efficient way.

Investigate how dynamic is this information If information rarely changes or is static it may be better to pre-create or cache it. There are many …

Hi Kettle fans,

The 2.3.1 release has been dropped! These kinds of things are always a bit troublesome because of the testing I need to do to get it out, the documentation updates, etc, etc. It’s also the right time for me to do bug administration: clean up old stuff, etc. This is not the most enjoyable type of activity and I’m glad it’s over and done with.

Anyway, it’s about time we did something enjoyable again: a webinar! Yes, we have been invited to do a MySQL Webinar next Tuesday. At 10:00 am Pacific, 1:00 pm Eastern or 17:00 GMT for the people living in my timezone. The presentation will be approximately 45 minutes long followed by Q&A.

I think that this is a great …

SQLyog is now free software and seems to run under WINE on my Ubuntu box okay (i think i’m running stock wine from dapper….)

So those of you addicted to SQLyog (i hear there’s a couple), you can use it on linux okay too (and probably other systems that WINE supports)

By allison

Webyog, based in Bangalore, India, has open sourced SQLyog, their GUI MySQL management tool. They're following MySQL's dual licensing model, split between a Community Edition under the GPL and a proprietary Enterprise Edition. In an interview with one of the founders, they estimate about 10% of code in the Enterprise Edition isn't released in the Community Edition.

In the past, I've heard (mainly from friends and collegues based in India or who visit regularly promoting open source) that India is more a consumer than a producer of open source software. Perhaps that's changing.

I also wonder if …

I got bitten by an implicit type conversion with MySQL recently. This article is about avoiding implicit type conversions, and the weird places you might find them. Introduction Here’s the setup: I was trying to select every day in the current month. Have you read my article about how stringifying dates is faster than comparing them as dates? At the time I wrote this query, I was experimenting with such things after seeing my co-workers do it a lot, for example, taking the left ten characters of a timestamp column to get the date part of it.

internals, optimization triggers One thing about TRIGGERs is that you can have 6 triggers per table:

That was brought up at the User Group meeting.

In other news, I have been reading the MySQL Internals Manual. The most interesting chapter yet is Chapter 3, the optimizer. This can be read out of order, and actually contains lots of information …

One common request I see is for better integration between MySQL and Visual Studio, allowing developers to manage MySQL databases from within VS. Well, the wait is over, announcement to follow:

Today we have released the first build of MySQL Tools for Microsoft Visual Studio — a downloadable plug-in for Visual Studio 2005 that allows Windows developers to quickly build MySQL data-driven applications with Visual Studio. With this plug-in, developers will be able to create, modify and manage MySQL database objects with an easy-to-use interface from within the Visual Studio IDE. This product is delivered as a package compatible with Visual Studio 2005 and delivers the following features:

• DDEX (Data Designer Extensibility) compatibility
• Ability to work with MySQL objects (tables, views, stored procedures, etc) from within Server Explorer

In a later release, we are planning on including tighter …

Today I was asked an interesting question: "Can we make MySQL to be case sensitive for SELECT queries and can we force a column in MySQL to be always lowercase?"

My response was that yes, we can have "instruct" MySQL to be case sensitive. One way to do that is to set the collation for the table (or column) to be either binary or case sensitive as shown below.

The naming convention for collation in mysql is as follows:

*_bin: represents binary case sensitive collation
*_cs: case sensitive collation
*_ci: case insensitive collation

###########
# Start binary collation example
###########
mysql> create table case_bin_test (word VARCHAR(10)) CHARACTER SET latin1 COLLATE latin1_bin;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO case_bin_test VALUES ('Frank'),('Google'),('froogle'),('flickr'),('FlicKr');
Query OK, 5 rows affected (0.00 …