Fulltext problem

[skydancer]

Hello all!

I'm trying to get MySQL FULLTEXT search working... My board is fairly big (2.5M posts).

Switching this in VB Admin panel -> "Search Type" didn't finish because the creation of the indexes took longer than PHP's max execution time (around 10 minutes).

I've created the indexes manually by issuing the following SQL commands on the MySQL prompt, as suggested:

ALTER TABLE vb3_post ADD FULLTEXT INDEX (title,pagetext)
ALTER TABLE vb3_thread ADD FULLTEXT INDEX (title)

This seemed to have succeeded, because I can view that the indexes are there:

mysql> show index from vb3_thread;
+------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table      | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| vb3_thread |          0 | PRIMARY      |            1 | threadid    | A         |      151063 |     NULL | NULL   |      | BTREE      |         |
| vb3_thread |          1 | forumid      |            1 | forumid     | A         |          80 |     NULL | NULL   |      | BTREE      |         |
| vb3_thread |          1 | forumid      |            2 | visible     | A         |          98 |     NULL | NULL   |      | BTREE      |         |
| vb3_thread |          1 | forumid      |            3 | sticky      | A         |         151 |     NULL | NULL   |      | BTREE      |         |
| vb3_thread |          1 | forumid      |            4 | lastpost    | A         |      151063 |     NULL | NULL   |      | BTREE      |         |
| vb3_thread |          1 | postuserid   |            1 | postuserid  | A         |       21580 |     NULL | NULL   |      | BTREE      |         |
| vb3_thread |          1 | postuserid_2 |            1 | postuserid  | A         |       21580 |     NULL | NULL   |      | BTREE      |         |
| vb3_thread |          1 | pollid       |            1 | pollid      | A         |        3975 |     NULL | NULL   |      | BTREE      |         |
| vb3_thread |          1 | lastpost     |            1 | lastpost    | A         |      151063 |     NULL | NULL   |      | BTREE      |         |
| vb3_thread |          1 | lastpost     |            2 | forumid     | A         |      151063 |     NULL | NULL   |      | BTREE      |         |
| vb3_thread |          1 | dateline     |            1 | dateline    | A         |      151063 |     NULL | NULL   |      | BTREE      |         |
| vb3_thread |          1 | title        |            1 | title       | A         |       37765 |     NULL | NULL   |      | FULLTEXT   |         |
+------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
12 rows in set (0.00 sec)

mysql> show index from vb3_post;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| vb3_post |          0 | PRIMARY  |            1 | postid      | A         |     2489113 |     NULL | NULL   |      | BTREE      |         |
| vb3_post |          1 | userid   |            1 | userid      | A         |       32751 |     NULL | NULL   |      | BTREE      |         |
| vb3_post |          1 | threadid |            1 | threadid    | A         |      146418 |     NULL | NULL   |      | BTREE      |         |
| vb3_post |          1 | threadid |            2 | userid      | A         |     1244556 |     NULL | NULL   |      | BTREE      |         |
| vb3_post |          1 | title    |            1 | title       | A         |      829704 |     NULL | NULL   |      | FULLTEXT   |         |
| vb3_post |          1 | title    |            2 | pagetext    | A         |      829704 |     NULL | NULL   |      | FULLTEXT   |         |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
6 rows in set (0.00 sec)

Nevertheless, when I go to the vb3 admin panel, it still displays "Your forum is currently using the vBulletin search engine.".

Also, searches return no results now, because I've emptied vBulletin's own search indexes.

Any ideas?

Cheers!
sd

[Freddie Bingham]

You need to enter debug mode by placing

$config['Misc']['debug'] = true;

in your config.php

Next go into your admin cp, vBulletin Options. You will see a new Version Info and Other Untouchables section at the top. Change the Fulltext Search to 'Yes'

You can also just go through the search switching process again. It should see that you have the fulltext indices in place and not try to create them again.

[skydancer]

This did the trick!

Thanks!
sd