Database Zone

Did you ever wonder how the MySQL fulltext indexing works? I mean the mathematics behind MySQL's fulltext index feature. At the Epsilon-Delta weblog there is an article by Ted Dziuba entitled "Dissecting MySQL Fulltext Indexing" that explains the mathematics behind Fulltext searching and what it can and cannot do.

Fulltext Indexing is a method by which MySQL quantifies the relevance of textual entries to an input query. The concept is similar to the way Google quantifies the importance of pages on the web with PageRank. However, in the context of a database, the requirements of the method are a little different.

» Read More

Beginning with MySQL 5.1.5, two functions providing basic XPath (XML Path Language) capabilities are available: ExtractValue and UpdateXML. For detailed informations about these 2 functions and for a basic description of XPath see the MySQL manual here: http://dev.mysql.com/doc/refman/5.1/en/xml-functions.html

ExtractValue is the "MySQL SELECT" equivalent for XML documents and UpdateXML as its name says is the "MySQL UPDATE" equivalent for XML documents.

» Read More

Here are some of the best free MySQL tools out there. The description is not my review of the product, it is just what the developers say about their tools. If you know some other good ones, you can drop a comment.

» Read More

The begining of this story is a PHPNuke module. The module has a block that picks a random item from a database table and displays it. The content of the table never changes. Here is how the programmer of the block does it:

He knows that there are 6899 rows because the last id in the table has a value of 6899. So with the "rand" PHP function, he picks a random number from 1 to 6899 and then queries the database table with a WHERE clause that checks if the value of the id is equal to the picked number:

» Read More

The "Howto Forge" site has published yesterday a good article entitled "How To Set Up Database Replication In MySQL". It is a step by step tutorial about MySQL replication feature. The article discribes all required steps on systems running "Debian Sarge", however the configuration should apply to almost all distributions with little or no modification.

» Read More

Do you know that the mysql command line tool has --i-am-a-dummy option? It's an option for beginners that has the same effect as --safe-updates. The --i-am-a-dummy option is very helpful because it prevents accidents. If you execute the SQL statement:

DELETE FROM table_name

what happens? You delete all rows from the table. With the --i-am-a-dummy option:

» Read More

This tip is very helpful specially in data transmission between systems.

Suppose we have a database named "mydb".

>>mysql -uyour_login_name -pyour_password
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8 to server version: 4.0.25-nt-max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

Change your_login_name and your_password with your mysql's login name and password.

» Read More