title: Fulltext search for django : Mysql not so bad ? (vs sphinx, xapian)
body: <p>I am studying fulltext search engines for django.
  It must be simple to install, fast indexing, fast index update, not blocking while indexing, fast search.</p>
  
  <p>After reading many web pages, I put in short list :
  Mysql MYISAM fulltext, djapian/python-xapian, and django-sphinx
  I did not choose lucene because it seems complex, nor haystack as it has less features than djapian/django-spĥinx (like fields weighting).</p>
  
  <p>Then I made some benchmarks, to do so, I collected many free books on the net to generate a database table with 1 485 000 records (id,title,body), each record is about 600 bytes long.
  From the database, I also generated a list of 100 000 existing words and shuffled them to create a search list. For the tests, I made 2 runs on my laptop (4Go RAM, Dual core 2.0Ghz): the first one, just after a server reboot to clear all caches, the second is done juste after in order to test how good are cached results. Here are the "home made" benchmark results :</p>
  
  <pre><code>1485000 records with Title (150 bytes) and body (450 bytes)
  
  Mysql 5.0.75/Ubuntu 9.04 Fulltext :
  ==========================================================================
  
  Full indexing : 7m14.146s
  
  1 thread, 1000 searchs with single word randomly taken from database : 
  First run : 0:01:11.553524
  next run : 0:00:00.168508
  
  Mysql 5.5.4 m3/Ubuntu 9.04 Fulltext :
  ==========================================================================
  
  Full indexing : 6m08.154s
  
  1 thread, 1000 searchs with single word randomly taken from database : 
  First run : 0:01:09.553524
  next run : 0:00:20.316903
  
  1 thread, 100000 searchs with single word randomly taken from database : 
  First run : 9m09s
  next run : 5m38s
  
  1 thread, 10000 random strings (random strings should not be found in database) :
  just after the 100000 search test : 0:00:15.007353
  
  1 thread, boolean search : 1000 x (+word1 +word2) 
  First run : 0:00:21.205404
  next run : 0:00:00.145098
  
  Djapian Fulltext : 
  ==========================================================================
  
  Full indexing : 84m7.601s
  
  1 thread, 1000 searchs with single word randomly taken from database with prefetch : 
  First run : 0:02:28.085680
  next run : 0:00:14.300236
  
  python-xapian Fulltext :
  ==========================================================================
  
  1 thread, 1000 searchs with single word randomly taken from database : 
  First run : 0:01:26.402084
  next run : 0:00:00.695092
  
  django-sphinx Fulltext :
  ==========================================================================
  
  Full indexing : 1m25.957s
  
  1 thread, 1000 searchs with single word randomly taken from database : 
  First run : 0:01:30.073001
  next run : 0:00:05.203294
  
  1 thread, 100000 searchs with single word randomly taken from database : 
  First run : 12m48s
  next run : 9m45s
  
  1 thread, 10000 random strings (random strings should not be found in database) :
  just after the 100000 search test : 0:00:23.535319
  
  1 thread, boolean search : 1000 x (word1 word2) 
  First run : 0:00:20.856486
  next run : 0:00:03.005416
  </code></pre>
  
  <p>As you can see, Mysql is not so bad at all for fulltext search. In addition, its query cache  is very efficient.</p>
  
  <p>Mysql seems to me a good choice as there is nothing to install (I need just to write a small script to synchronize an Innodb production table to a MyISAM search table) and as I do not really need advanced search feature like stemming etc...</p>
  
  <p>Here is the question : What do you think about Mysql fulltext search engine vs sphinx and xapian ?</p>