Unified Index with MySQL Full Text Search

Background info: Unified Index Comparison and Search dilemma.

To test:

1. Assign the "Server Load" module to your site via tiki-admin_modules.php
   • This provides Execution time, Memory usage and number of Queries
2. Do a test upgrade to trunk or use one of the Pre-dogfood servers
3. Set Unified Index engine to MySQL at tiki-admin.php?page=search
4. Click "Change Preferences"
5. Proceed to a full re-index (tiki-admin.php?page=search&rebuild=now)
   • You can check via PhpMyAdmin or Adminer that a new MySQL table was created for this. If the table disappears, there is likely an error preventing the re-index to complete
   • For larger sites, use the command line
6. Visit tiki-searchindex.php and tiki-searchresults.php and attempt a search and compare result quality (relevancy) and speed of:
   • tiki-searchresults.php of your upgraded site
   • tiki-searchindex.php of your upgraded site
   • tiki-searchresults.php of your old site
7. Indicate your results below

Problem

"temp/Search_Indexer.log" cannot be opened with mode "w" (and zero bytes)

root@server:/path# php console.php index:rebuild --log
PHP Warning:  Module 'mysql' already loaded in Unknown on line 0
root@server:/path#

root@server:/path# php console.php index:rebuild --log
PHP Warning:  Module 'mysql' already loaded in Unknown on line 0
PHP Warning:  in_array() expects parameter 2 to be array, null given in /path/templates_c/en_classic^5ce0751bd7471be04787252a46a8f519d1dfa97e.file.wikiplugin_mail.tpl.php on line 113
(...)
PHP Warning:  in_array() expects parameter 2 to be array, null given in /path/templates_c/en_classic^5ce0751bd7471be04787252a46a8f519d1dfa97e.file.wikiplugin_mail.tpl.php on line 190
(...)
root@server:/path#

Search index could not be rebuilt.
"temp/Search_Indexer.log" cannot be opened with mode "w"
No index available.

Success

nextdev.tiki.org 2013-08-27 with all plugins off

• wiki page: 879
• blog post: 22
• file: 262
• trackeritem: 4327
• sheet: 15
• comment: 3003
• Execution time: 113.91 secs
• Memory usage: 116.67MB
• Queries: 128166 in 21.2 secs
• Last update from SVN (12.0svn): Tuesday 27 August, 2013 01:29:15 UTC- REV 47286

nextdev.tiki.org 2013-09-25 with only some plugins excluded

• wiki page: 884
• blog post: 22
• file: 287
• trackeritem: 4393
• sheet: 15
• comment: 3068
• Execution time: 197.58 secs
• Memory usage: 114.31MB
• Queries: 130303 in 99.9 secs
• Last update from SVN (12.0svn): Wednesday 25 September, 2013 11:05:12 CEST- REV 47729

Marc dataset

• wiki page: 2850
• blog post: 4
• article: 1708
• file: 4321
• trackeritem: 6988
• comment: 13

• Execution time: 510.07 secs
• Memory usage: 116.50MB
• Queries: 245172 in 163. secs
• Last update from SVN (12.0svn): Friday 02 of August, 2013 11:11:40 EDT- REV 46958

This is really good. The same database takes quite a while to index with Zend_Lucene and thus, must be done via the command line

Marc dataset again (something is a lot faster than 3 weeks ago)

• wiki page: 2850
• blog post: 4
• article: 1708
• file: 4321
• trackeritem: 6988
• comment: 13

• Execution time: 203.88 secs
• Memory usage: 36.35MB
• Queries: 187378 in 79.9 secs
• Last update from SVN (12.0svn): Wednesday 21 of August, 2013 15:09:27 EDT- REV 47185

The same database with php console.php index:rebuild --log and Zend_Lucene took 38 minutes so MySQL as storage is 10 times faster to index

nextdoc.tiki.org 2013-08-03

• wiki page: 1823
• blog post: 6
• article: 19
• file: 778
• trackeritem: 186
• sheet: 6
• comment: 222

• Execution time: 268.86 secs
• Memory usage: 139.56MB
• Queries: 65140 in 124. secs

nextdoc.tiki.org 2013-08-04

• wiki page: 1824
• blog post: 6
• article: 19
• file: 778
• trackeritem: 186
• sheet: 6
• comment: 222

• Execution time: 127.69 secs
• Memory usage: 132.51MB
• Queries: 64838 in 26.3 secs
• (12.0svn): Saturday 03 August, 2013 20:29:06 CEST- REV 46982

precarios.org (testing it out of multitiki). 2013-08-04. Xavi

• Wiki Pages: 981
• Files: 1343
• articles: 224
• Total forum topics: 6618
• Total forum replies: 55173
• Trackers: Over 2000 items in 53 trackers.
• Users: 8606

tiki-searchresults.php with Mysql full text search

• Execution time: 0.79 secs
• Memory usage: 39.82MB
• Queries: 302 in 0.42 secs
• (12.0svn): Saturday 03 August, 2013 20:29:06 CEST- REV 46982

tiki-searchresults.php with Mysql full text search

• Execution time: 0.40 secs
• Memory usage: 43.85MB
• 305 database queries used in 0.0 secs
• (9.0svn): Rev. 46851. Last Changed Date: 2013-07-25 21:15:56 +0200 (dj, 25 jul 2013)

tiki-searchindex.php

• Execution time: 3.61 secs
• Memory usage: 50.92MB
• Queries: 345 in 2.78 secs
• (12.0svn): Saturday 03 August, 2013 20:29:06 CEST- REV 46982

tiki-searchindex.php

• Execution time: 0.79 secs
• Memory usage: 49.31MB
• Queries: 156 in 0.0 secs
• (9.0svn): Rev. 46851. Last Changed Date: 2013-07-25 21:15:56 +0200 (dj, 25 jul 2013)

intercanvis.net. 2013-08-05. Xavi

This error is shown dozens of times when running the rebuild of the index in the command line ( php console.php index:rebuild --log
):

PHP Warning:  in_array() expects parameter 2 to be array, null given in /path/templates_c/en_classic^5ce0751bd7471be04787252a46a8f519d1dfa97e.file.wikiplugin_mail.tpl.php on line 113
PHP Warning:  in_array() expects parameter 2 to be array, null given in /path/templates_c/en_classic^5ce0751bd7471be04787252a46a8f519d1dfa97e.file.wikiplugin_mail.tpl.php on line 113
(...)
PHP Warning:  in_array() expects parameter 2 to be array, null given in /path/templates_c/en_classic^5ce0751bd7471be04787252a46a8f519d1dfa97e.file.wikiplugin_mail.tpl.php on line 190
PHP Warning:  in_array() expects parameter 2 to be array, null given in /path/templates_c/en_classic^5ce0751bd7471be04787252a46a8f519d1dfa97e.file.wikiplugin_mail.tpl.php on line 190
(...)

• Wiki Pages: 310
• Files: 483
• Images: 173
• articles: 93
• Total forum topics: 117
• Total forum replies: 232
• Trackers: Over 8000 items across 20 trackers.
• Users: 2361

tiki-searchresults.php 12.x (Basic Search: with Mysql full text search)

• Results found
• Execution time: 1.25 secs
• Memory usage: 45.98MB
• Queries: 772 in 0.73 segs
• (12.0svn): Saturday 03 August, 2013 20:29:06 CEST- REV 46982

tiki-searchindex.php 9.x (Basic search: Mysql full text search disabled; Advanced Search with Lucene PHP Implementation)

• Results found.
• Execution time: 1.47 segs
• Memory usage: 59.13MB
• Queries: 1275 in 0.1 segs
• (9.0svn): Rev. 46851. Last Changed Date: 2013-07-25 21:15:56 +0200 (dj, 25 jul 2013)

tiki-searchindex.php 12.x (Basic Search: Mysql full text search enabled; Advanced search: Unified index, with Lucene PHP)

• Results found.
• Execution time: 2.05 segs
• Memory usage: 61.57MB
• Queries: 1144 in 0.47 segs
• (12.0svn): Saturday 03 August, 2013 20:29:06 CEST- REV 46982

tiki-searchindex.php 12.x (Basic Search: Mysql full text search disabled; Advanced search: Unified index, with Mysql full text search selected)

• No results found!
• Execution time: 1.05 segs
• Memory usage: 52.83MB
• Queries: 609 in 0.26 segs
• (12.0svn): Saturday 03 August, 2013 20:29:06 CEST- REV 46982

tiki.org 2014-07-30

• wiki page: 3196
• forum post: 14388
• blog post: 296
• article: 163
• file: 496
• trackeritem: 4211
• sheet: 8
• comment: 2524
• user: 21906
• activity: 0

• Execution time: 528.50 secs
• Memory usage: 163.37MB
• Queries: 844567 in 434. secs
• Server load: 0.3

• Exclude all plugins: y
• Memory limit to apply during search index rebuild: 1000M
• Time limit to apply during search index rebuild: 1500 seconds
• Users available in search results: y

Unified Search with MySQL Full Text Search