Advanced MySQL Features

Posted by kumarjit in Software, Web Hosting Tips

1 Star2 Stars (No Ratings Yet)

Advanced MySQL Features

ADVANCED MySQL FEATURES

MySQL is a powerful RDMS platform. The basic functions are more than sufficient to carry out most of the regular, day to day requirements of developing a suitable query for any purpose. However, MySQL also has certain advanced features, which are not usually utilized in basic, ordinary queries, but find application in certain occasions when the basic functions fail to provide the required functionality. Certain special or advanced feature of MySQL have been mentioned below.

My SQL 3 introduced the concept of search on text elements in a text field, with the FULTEXT index. The FULLTEXT indexing allows us to compare a certain text field, specifically in a particular column, to any required string which is mentioned in the query.
For example, the following code for CREATE-ing a Document table will be like this

CREATE TABLE Document(
url           VARCHAR(150) NOT NULL PRIMARY KEY,
title        VARCHAR(100) NOT NULL,
FULLTEXT(title)
);

A simple query like the following:

SELECT title FROM Document
WHERE MATCH(title) AGAINST (‘MySQL’);

This will be enough to search the whole title field for possible matches. However, it is to be taken care of that the title and any other field mentioned in the FULLTEXT statement should be structured according to the index, and not against the columns. The search is case insensitive. The results with most relevance are displayed, while those with very remote relevance are discarded.

Table locking is locking down a group of tables so that only a single or limited number of clients can gain access to it. Even though the type of table does not from a limiting factor, any action, once taken, cannot be rolled back. Locking of a table has two advantages- firstly, a group of statements can be executed for a group of tables, as one unit of work, and secondly, multiple updates can occur faster under certain conditions.

There are three kinds of locks supported by MySQL: read, write and read local. Both the read locks lock down a table, so that the clients can only view it, but cannot alter or add anything. Read local lock differs in the property that it allows a local client to execute non-conflicting INSERT statements, as long as there is no change to the MySQL files, from any external agents, that is, from outside MySQL. Write locks the specified table against all access- either read or write. However, it is very important to unlock the locks, as long lived data locks are supposed to reduce database performance.

Even though date functions are the most commonly used ones in MySQL, we can do a lot more with a little bit of modification to the query. For example, MySQL can be used to calculate the time  between two dates by specifying them is this format:

SELECT TO_DAYS(NOW( )) – TO_DAYS(‘2010-11-10’);

This query will calculate the number of days in the millennium that have already passed. The TO_DAYS() returns the number of days since 1 B.C.

String functions like LENGTH() are among the most commonly used commands in SQL. However, there are certain advanced string functions, like the SOUNDEX, which are rarely applied. The SOUNDEX() function essentially converts a word into its soundex representation. This is advantageous as it helps to find out if any one of the input strings have been misspelt, by comparing the two input strings.

Get the best of MySQL hosting  | MySQL web hosting only on Bounceweb hosting!

Be Sociable, Share!

Leave a Reply

You must be logged in to post a comment.