Configuring and carrying out a FULLTEXT search in MySQL

When we normally have an application associated with a MySQL database and we want to carry out a search, we would normally go for the yucky “LIKE%” syntax when writing our search query. What if I told you that there is a much better indexed method built right in to MySQL that you could use to run an optimised and effective search? Welcome to the world of MySQL Fulltext searching. Today we’ll discuss how to define Fulltext indices on a MySQL database table and how to run a simple search query on them.

First things first, let’s install PhpMyAdmin. I’m going to assume that you already have MySQL installed. PhpMyAdmin is a handy tool that lets us manage our MySQL databases without having to go through the hassle of typing out long terminal commands. You can use any other similar GUI tool such as MySQL Workbench as well.

Now, I have a database called etutor where I have a table called questions. In order to set up Fulltext indices on this table, I’m going to have to convert it to a MyISAM-engine-based table. This can be achieved by selecting the table and going to the ‘Operations’ tab:

You need to change your database table’s engine to MyISAM

When I open up the structure of the questions table in PhpMyAdmin, there’s a small ‘fulltext’ icon on the right of each attribute of the table.

The Fulltext button can be seen next to each column name

This is the button with the little ‘T’ on it. Clicking on this will define that attribute (it needs to be VARCHAR or Text) type as a Fulltext index, which we can search on.

Expanding the ‘Details’ section at the bottom of the page will show you all the Fulltext indices you’ve defined on your table.

Its that simple to define a Fulltext index on your database table! Now let’s move on to some searching.

My search query will utilise the ‘MATCH’ and ‘AGAINST’ keywords in MySQL to search through my Fulltext indices:

SELECT * FROM questions WHERE MATCH(subject)
AGAINST('SEARCH_QUERY') OR MATCH(tags)
AGAINST('SEARCH_QUERY');

And frankly, it’s that simple to search through those pesky text indices too. You will find that there is a massive performance boost when using Fulltext search vs. the traditional ‘LIKE%’ query approach.

A Singleton Java class for MySQL DB connection

I generally use MySQL along with my Java applications, and re-use a Singleton class I created a while back to connect to the database. It contains insert and query methods, but apart from that, it’s pretty basic. Just thought it might be useful for someone if I post it up here.

import com.mysql.jdbc.Connection;
import java.sql.*;
import java.sql.DriverManager;
/**
 * @desc A singleton database access class for MySQL
 * @author Ramindu
 */
public final class MysqlConnect {
    public Connection conn;
    private Statement statement;
    public static MysqlConnect db;
    private MysqlConnect() {
        String url= "jdbc:mysql://localhost:3306/";
        String dbName = "database_name";
        String driver = "com.mysql.jdbc.Driver";
        String userName = "username";
        String password = "password";
        try {
            Class.forName(driver).newInstance();
            this.conn = (Connection)DriverManager.getConnection(url+dbName,userName,password);
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
        }
    }
    /**
     *
     * @return MysqlConnect Database connection object
     */
    public static synchronized MysqlConnect getDbCon() {
        if ( db == null ) {
            db = new MysqlConnect();
        }
        return db;

    }
    /**
     *
     * @param query String The query to be executed
     * @return a ResultSet object containing the results or null if not available
     * @throws SQLException
     */
    public ResultSet query(String query) throws SQLException{
        statement = db.conn.createStatement();
        ResultSet res = statement.executeQuery(query);
        return res;
    }
    /**
     * @desc Method to insert data to a table
     * @param insertQuery String The Insert query
     * @return boolean
     * @throws SQLException
     */
    public int insert(String insertQuery) throws SQLException {
        statement = db.conn.createStatement();
        int result = statement.executeUpdate(insertQuery);
        return result;

    }

}

Cheers!