Archive

Posts Tagged ‘mysql’

Change the full text index minimum word length with MySQL

August 14, 2009 5 comments

The MySQL full text index by default only indexes words which are 4 characters or longer, which means on a blog like mine if you search for “PHP” nothing will be returned. This post shows how to change minimum word length in MySQL so words or 3 characters (or even 2 if you want) can be indexed as well.
Edit the MySQL configuration file my.cnf which is usually located on a *nix box at /etc/my.cnf or /etc/mysql/my.cnf and add the following line under the [mysqld] section to change the default to 3:

ft_min_word_len = 3

If the ft_min_word_len value is already in the file then change the number to the minimum length required.

After making this change the MySQL server must be restarted for it to take effect. It is not possible to change the size with a set query (e.g. “SET ft_min_word_len = 3” which will result in the error “#1193 – Unknown system variable ‘ft_min_word_len'”).

Now that the minimum word length has changed, and new or updated records will use the new minimum word length, but existing records will not be affected. To rebuild the full text index on a column for an example table called my_table, run this query:

REPAIR TABLE my_table QUICK;

Advertisements
Categories: General Tags:

ALTER PRIMARY KEY in MySQL

July 17, 2009 1 comment

It’s two step procedure:
1. First Drop the existing primary key:
ALTER table dlyprice drop primary key;

2. Create new primary Key:
#ALTER TABLE dlyprice ADD PRIMARY KEY (co_code,exchng);

and if you want to ignore duplicates
then

ALTER IGNORE TABLE dlyprice ADD PRIMARY KEY (co_code,exchng);

Categories: General Tags:

COALESCE in Mysql

April 27, 2009 Leave a comment

This function is very useful if you are adding number value column and it may contain null value.
COALESCE(value,…)

Returns the first non-NULL value in the list, or NULL if there are no non-NULL values.

mysql> SELECT COALESCE(NULL,1);
-> 1
mysql> SELECT COALESCE(NULL,NULL,NULL);
-> NULL

Categories: General Tags: