Search code examples
mysqlfull-text-searchquery-optimizationlarge-data

MySQL help/suggestions on optimizing a query on large database

I have a MyISAM mysql table with:

CREATE TABLE IF NOT EXISTS `songs` (
  `rid` int(11) NOT NULL auto_increment,
  `aid` int(11) NOT NULL,
  `song_title` varchar(256) NOT NULL,
  `download_url` varchar(256) NOT NULL,
  PRIMARY KEY  (`rid`),
  UNIQUE KEY `download_url` (`download_url`),
  KEY `song_title` (`song_title`),
  FULLTEXT KEY `song_title_2` (`song_title`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1

It has around 14 million rows. It's my first time handling such a large database and I haven't really cared about optimizations earlier. I've been trying out various things to test speed and accuracy.

1) Full text

select song_title from songs 
where match (song_title) againt ('search term') limit 0, 50
-- This gives me very unreliable results but speed is good.

2) LIKE

select song_title from songs 
where song_title LIKE '%search term%' limit 0, 50
-- Moderate matching results, speed is good when the query is 
-- easily able to fetch the first 50 results... but when i 
-- search for a term that does not exist then... here is the result..
-- MySQL returned an empty result set (i.e. zero rows). ( Query took 107.1371 sec )

3) Multiple LIKE

select song_title from songs 
where song_title like '%word_1%' and 
      song_title like '%word_2%' and 
      song_title like '%word_3%' and 
      song_title like '%word_N%' LIMIT 0, 50;
-- It takes about 0.2 seconds when the search terms are easily found.
-- Ran this exact above query just now to find the execution time when 
-- no results are found.
-- MySQL returned an empty result set (i.e. zero rows). ( Query took 30.8625 sec )

What I am looking for are tips and suggestions on optimizing the database / query regards to speed and accuracy.

I cannot use another search engine like sphinx since I do not have access outside the root of the site and cannot ask the guys handling the server to set it up.

Solution

  • MySQL cannot create index which will cover LIKE '%word%' query with leading percent sign because of the nature of B-tree index which used for that type of index. It will use index for prefix searches like LIKE 'word%'. Please note, that LIKE queries are not covered by MySQL full text indexes at all. The only queries covered by MyISAM fulltext index are MATCH ... AGAINST ...

    Assuming your dataset size you do need an external search engine especially if you planning to increase amount of data you searching.

    I don't have much details on your hosting environment, but if you have an SSH access to the hosting server I believe you can install and run Sphinx as non-privileged user. Set location prefix with ./configure script to your home directory (but please make sure it's not accessible from the web) like this:

    ./configure --prefix=/path/to/your/home

    and then perform 

    make && make install

    Then create sphinx config as described in http://astellar.com/2011/12/replacing-mysql-full-text-search-with-sphinx/ and finally start the daemon by running searchd from command line:

    /path/to/your/home/bin/searchd

    Hope it helps.