When dealing with multicolumn indexes the order of the columns in the index definition is very important. Fpr example if you have such an index

INDEX (a,b,c)

and you issue a query such

... WHERE b>1000;

MySQL will not be able to use the index. In case ol multicolumn indexes MySQL will always use the left-most part of the index. So, the index will be used for example if the query would be:

... WHERE a>100 AND b>1000;

So, MySQL will use the index only for search conditions that involve the follwing columns:

• a ,b ,c
• a, b
• a

The mulitcolumn indexes are also useful in solving ORDER BY and GROUP BY operations.
Let’s see the folliwing example.
Here is my customer table (for sake of simplicity it’s just a subset of my real table).

corra@localhost> show create table customer\G …

Today I answered to a problem regarding fulltext indexes on an italian newsgroup. The guy was in trouble in building a multicolumn fulltext index. MySQL always said that a column cannot be part of the index. Why?

Remember that all the columns in a fulltext index must have the same charset and the same collation.
Let’s try if it is true!

Create a sample news table and try to build the fulltext index on (news_title, news_text):

mysql> CREATE TABLE news(
        -> id INT auto_increment,
        -> news_title VARCHAR(100),
        -> news_text TEXT,
        -> PRIMARY KEY(id))
        -> CHARSET latin1 COLLATE latin1_general_ci;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE FULLTEXT INDEX ft_idx ON news(news_title,news_text);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

Great, it works!

And now, change the charset of one of the field and try …

Today I attended to first MySQL AB conference in Milan (Italy). They have presented the new services and the new italian team.

It was also the occasion to meet some friend.

Well MySQL AB, enjoy your business in Italy.

Regarding my last post MySQL Administrator useful InnoDB graphs here are the explanations of some of the graphs.

idb storage page

• reads graph: number of reads; it monitors if the system is under heavy or light read access.
• pending reads graph: number of pending read requests to the underlying data file (the database must wait to gain access). If this value increases it could indicate serious contention issue. Seeing this graph always close to the bottom line it’s very good.
• writes graph: number of writes; it monitors if the system is under heavy or light write access.
• pending writes graph: number of pending write requests to the underlying data file. If this value climbs requires attention. Seeing this graph …

Here is a simple configuration file (XML) for MySQL Administrator client.
In addition to the the default graphs I added some new pages containing the most important status variables to monitoring and evaluate the performance of InnoDB.

The new pages are:
idb storage: reads, writes, doublewrites and fsync activity
idb logs:writes to innodb logs and file system activity
idb buffer pool: buffer pool activity
idb disk: pages and rows activity

Just download the file mysqladmin_health.xml.gz then
gunzip it and copy it in the following (default) directory:
(Windows) C:\Documents and Settings\UserName\Application Data\MySQL
(Linux) ~/.mysqlgui

I hope this could be …

Since few days ago I had no problem to perform my data backups using mysqlhotcopy.
Tonight I had an astonishing problem:

Invalid db.table name 'archivio.archivio`.`almanacco' at /usr/bin/mysqlhotcopy line 859
Invalid db.table name 'banner.banner`.`adv' at /usr/bin/mysqlhotcopy line 859

… and so on … for every database to backup.

It’s very strange that the db_name the script needs to access is ‘archivio.archivio’. There’s a duplication of the db name, I have a db named ‘archivio’ not ‘archivio.archivio’.

So, what’s the problem?
I didn’t change mysqlhotcopy during last few months, but I updated my ubuntu just few days ago and the new DBD version is 4.0004 instead of older 2.9xxx.
Newer versions of DBD::mysql seem to prefix the table names with the database name causing mysqlhotcopy to fail. This seems to happen after version 4.0003

Now I have just two solutions: …

If you have an Ubuntu 7.04 Feisty Linux Distribution and you are planning to install mysql-proxy in order to test the new proxy functionalities, maybe you will have some problem. I use Ubuntu 7.04 on my laptop, with all packets installed and configured correctly using Synaptic Packet Manager and, in fact, I had problems! If you won’t have problems … well … I’m unlucky!

First of all, there is not a packetized mysql-proxy for Ubuntu, so the only way to install it is to build it from the source you can get from MySQL website.

mysql-proxy needs the LUA scripting language in order to work correctly. LUA is the milestone, the tool to write configuration scripts to instructs mysql-proxy what to do receiving packets to and from the MySQL server. LUA is the key to achieve great tasks from …

I'm moving my blog from http://gnu.inter.it/blogs/ilcorra/archives/cat_mysqlen.html to http://blog.pandiani.com

My new MySQL related XML feed is http://blog.pandiani.com/category/mysqlen/feed

Please update your links

Thanks

Using MEMORY tables in MySQL is very useful; they are stored in memory and they are usually not so big in order to avoid a huge utilization of RAM. They are very fast.

MEMORY tables are often used to store session user data, chat messages and to store small frames of data from a bigger table in order to achieve great performance.

But, if you use them in a replication environment, you can have some problem due to their "volatility"; MEMORY table rows are lost when the server shuts down.

If you restart a slave host for some reason, you will have empty MEMORY tables on it, while the master host will have the correct ones.

How to populate automatically MEMORY tables at slave start up copying data from master host?

We could use something like LOAD TABLE table_name FROM MASTER, but it's deprecated, so we will use something else.

I have developed a stored procedure you have to create on the …

Using MEMORY tables in MySQL is very useful; they are stored in memory and they are usually not so big in order to avoid a huge utilization of RAM. They are very fast.

MEMORY tables are often used to store session user data, chat messages and to store small frames of data from a bigger table in order to achieve great performance.

But, if you use them in a replication environment, you can have some problem due to their “volatility”; MEMORY table rows are lost when the server shuts down.

If you restart a slave host for some reason, you will have empty MEMORY tables on it, while the master host will have the correct ones.

How to populate automatically MEMORY tables at slave start up copying data from master host?

We could use something like LOAD TABLE table_name FROM MASTER, but it’s deprecated, so we will use something else.

I have developed a stored procedure you have to create on …