Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have this query in our app, that searches a table for a song title using MATCH AGAINST and picks the most relevant results.

The table has around a million results and is growing.

The app has been running on a bare metal cPanel server with the default MySQL config, recently it's been moved to a VPS and the query has gone from taking around 2 seconds, to taking around 8.

SELECT trackcatalogue_request.id
  ,trackcatalogue_request.artistId
  ,trackcatalogue_request.albumId
  ,trackcatalogue_request.songTitle
  ,trackcatalogue_request.artistName 
  ,IF(trackcatalogue_request.songTitle = 'test', 1, 0) as trump
  ,MATCH(trackcatalogue_request.songTitle,trackcatalogue_request.artistName) AGAINST ('test') AS score
FROM trackcatalogue_request
WHERE (trackcatalogue_request.songTitle LIKE '%test%'
OR MATCH(trackcatalogue_request.songTitle,trackcatalogue_request.artistName) AGAINST ('test'))
AND (trackcatalogue_request.id NOT IN(/* Blacklisted ids*/))
AND (trackcatalogue_request.isBanned = 0) AND (trackcatalogue_request.isExplicit = 0)
AND (trackcatalogue_request.isXmas = 0)
GROUP BY trackcatalogue_request.artistId, trackcatalogue_request.songTitle
ORDER BY trump DESC, score DESC
LIMIT 200

We think it's because VPS's disk performance is worse.
Removing the GROUP or ORDER brings the query back down to 2 seconds which is acceptable...

How can we optimise this query?

Here's the EXPLAIN

1    SIMPLE    trackcatalogue_request    range    PRIMARY,idxIsExplicit,idxIsXmas,idxIsBanned    idxIsExplicit    1        1078658    Using where; Using temporary; Using filesort

And the SHOW CREATE TABLE

CREATE TABLE `trackcatalogue_request` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `albumId` bigint(20) DEFAULT NULL,
  `trackNo` int(11) DEFAULT NULL,
  `songTitle` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `artistName` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `artistId` bigint(20) DEFAULT NULL,
  `albumTitle` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `releaseYear` varchar(4) COLLATE utf8_unicode_ci DEFAULT NULL,
  `comment` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `genreId` int(11) DEFAULT NULL,
  `xReference` bigint(20) DEFAULT NULL,
  `entryDate` date DEFAULT NULL,
  `peakPosition` int(11) DEFAULT NULL,
  `top40` bigint(20) DEFAULT NULL,
  `preRelease` date DEFAULT NULL,
  `dateAdded` datetime DEFAULT NULL,
  `isExplicit` tinyint(4) NOT NULL,
  `isXmas` tinyint(4) NOT NULL,
  `isBanned` tinyint(4) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idxArtistNameSimple` (`artistName`),
  KEY `idxSongTitleNormal` (`songTitle`),
  KEY `idxIsExplicit` (`isExplicit`),
  KEY `idxIsXmas` (`isXmas`),
  KEY `idxIsBanned` (`isBanned`),
  KEY `idxArtistId` (`artistId`),
  KEY `idxAlbumId` (`albumId`),
  KEY `idxGenreId` (`genreId`),
  KEY `idxTrackNo` (`trackNo`),
  FULLTEXT KEY `idxArtistName` (`artistName`),
  FULLTEXT KEY `idxSongTitle` (`songTitle`),
  FULLTEXT KEY `idxSongTitleArtistName` (`songTitle`,`artistName`),
  FULLTEXT KEY `idxAlbumTitle` (`albumTitle`)
) ENGINE=MyISAM AUTO_INCREMENT=1085946 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
share|improve this question
    
Please show the explain plan and the SHOW CREATE TABLE output here. As an aside, 'filesort' doesn't mean it's sorted on disk. Check here –  Derek Downey Jun 13 '13 at 12:25
    
Hey @DerekDowney, i've update the question with those things.. thanks! –  Daniel Upton Jun 13 '13 at 12:40
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.