How to optimize MySQL table containing 1.6+ million records for LIKE '%abc%' querying

Question

I have a table with this structure and it currently contains about 1.6 million records.

CREATE TABLE `chatindex` (
    `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `roomname` varchar(90) COLLATE utf8_bin NOT NULL,
    `username` varchar(60) COLLATE utf8_bin NOT NULL,
    `filecount` int(10) unsigned NOT NULL,
    `connection` int(2) unsigned NOT NULL,
    `primaryip` int(10) unsigned NOT NULL,
    `primaryport` int(2) unsigned NOT NULL,
    `rank` int(1) NOT NULL,
    `hashcode` varchar(12) COLLATE utf8_bin NOT NULL,
    PRIMARY KEY (`timestamp`,`roomname`,`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Both the roomname and username columns can contain the same exact data, but the uniqueness and the important bit of each item comes from combining the timestamp with those two items.

The query that is starting to take a while (10-20 seconds) is this:

SELECT timestamp,roomname,username,primaryip,primaryport 
    FROM `chatindex`
    WHERE username LIKE '%partialusername%'

What exactly can I do to optimize this? I can't do partialusername% because for some queries I will only have a small bit of the center of the actual username, and not the first few characters from the beginning of the actual value.

Edit:

Also, would sphinx be better for this particular purpose?

Answer 1

Use Fulltext indexes , these are actually designed for this purpose. Now InnoDb support fulltext indexes in MySQL 5.6.4.

Answer 2

1. Create Index on table column username (full-text indexing).
2. As an idea, you can create some views on this table that will contain filtered data on the basis of alphabets or other criteria and based on that your code will decide which view to use to fetch the search results.