MySQL search optimization

The goal of this post is to implement a search mechanism that searches for songs and artists in a playlist table, that has about 11 million entries. All data has been gathered from a third party application that provides a history of the songs played on Austrian radio stations. One entry consists of the play date, the title and the artist. Unfortunately the data has to be downloaded as an html file that can be converted into SQL statements later on but does not have any structure.

Thanks to @davidkroell for the help concerning the database optimization.

Storing the data

At the beginning all the data had to be stored in a MySQL database. Therefore 2 tables were created: radio_stations and songs.

Radio Stations:

The table radio_stations was created using the following SQL statement. The radio stations were inserted manually and contain and id, an abbreviation that is used when downloading new data from the third party application and a display name.

CREATE TABLE `radio_stations` (
    `id` INT AUTO_INCREMENT,
    `abbreviation` VARCHAR(20) NOT NULL,
    `name` VARCHAR(100) NOT NULL,
    CONSTRAINT pk_radio_stations PRIMARY KEY (id)
);

Songs:

During the first iteration the table songs had the following columns. This table has not been normalized because of the sloppy input data and also for benchmarks. All data has been stored by using generated SQL files from the downloaded HTML file. To convert the HTML into an SQL file a custom bash script was used that replaced the HTML syntax with SQL syntax by using sed.

CREATE TABLE `songs` (
    `id` INT AUTO_INCREMENT,
    `radio_station_id` INT NOT NULL,
    `play_date` DATETIME NOT NULL,
    `title` VARCHAR(255) NOT NULL,
    `artist` VARCHAR(255) NOT NULL,
    constraint pk_songs primary key (id),
    constraint fk_songs_radio_stations foreign key (radio_station_id)
    references radio_stations(id)
);

Search

The first search implementation only allowed searching for song titles and not for artists. This has been realized using the following query (simple but inefficient).

SELECT title, artist, COUNT(*) as count FROM songs
WHERE radio_station_id = 1
AND title like "%test%"
GROUP BY title, artist, radio_station_id
ORDER BY count DESC;

Benchmarks:

Optimization 01 - Cached table

The first optimization was a cache table that stores all grouped songs per radio station. Therefore the group by can be left away. To insert the grouped entries the following statement has been used:

-- CREATE TABLE
CREATE TABLE songs_per_radio_station (
    id INT AUTO_INCREMENT,
    title VARCHAR(255),
    artist VARCHAR(255),
    radio_station_id INT NOT NULL,
    count INT NOT NULL,
    CONSTRAINT pk_songs_per_radio_station PRIMARY KEY (id),
    CONSTRAINT fk_songs_per_radio_station_radio_station FOREIGN KEY (radio_station_id)
        REFERENCES radio_stations (id)
);

-- INSERT DATA
INSERT INTO songs_per_radio_station
SELECT null as id, title, artist, radio_station_id, count(*) as count
FROM songs
GROUP BY title, artist, radio_station_id
ORDER BY radio_station_id asc, count desc;

Afterwards the following statement can be used to search for a title:

SELECT * FROM songs_per_radio_station
WHERE radio_station_id = 1
AND title like "%test%"
ORDER BY count DESC;

Optimization 02 - Fulltext Index

After creating a cache table a fulltext index has been used on the cached table. This allows the user to search for songs and artists. The best matching case will be shown first and afterwards the entries are sorted by count.

-- CREATE TABLE
CREATE TABLE songs_per_radio_station (
    id INT AUTO_INCREMENT,
    title VARCHAR(255),
    artist VARCHAR(255),
    radio_station_id INT NOT NULL,
    count INT NOT NULL,
    FULLTEXT (title, artist),
    CONSTRAINT pk_songs_per_radio_station PRIMARY KEY (id),
    CONSTRAINT fk_songs_per_radio_station_radio_station FOREIGN KEY (radio_station_id)
        REFERENCES radio_stations (id)
);

-- INSERT DATA
INSERT INTO songs_per_radio_station
SELECT null as id, title, artist, radio_station_id, count(*) as count
FROM songs
GROUP BY title, artist, radio_station_id
ORDER BY radio_station_id asc, count desc;

Songs and artists can be searched using the following query:

SELECT id, title, artist, count
FROM songs_per_radio_station
WHERE radio_station_id = 1
AND MATCH (title, artist) AGAINST ("test")
LIMIT 30
OFFSET 0;

These outcomes lead to the final result which is a performance improvement with a maximum request time reduction of 1/100 of the original request duration.