Manual talk:Searchindex table

Migrating from MyISAM to InnoDB

Hi,

I'm currently migrating a large mediawiki from MyISAM to InnoDB, testing this on a development database. When trying to convert searchindex it fails because FULLTEXT isn't supported by InnoDB.

I may leave this table on MyISAM this time but how would MediaWiki create this table in a InnoDB-only environment? What type of INDEX would be used here?

Currently the table looks like this:

mysql> show create table searchindex\G
*************************** 1. row ***************************
       Table: searchindex
Create Table: CREATE TABLE `searchindex` (
  `si_page` int(8) unsigned NOT NULL DEFAULT '0',
  `si_title` varchar(255) NOT NULL DEFAULT '',
  `si_text` mediumtext NOT NULL,
  UNIQUE KEY `si_page` (`si_page`),
  FULLTEXT KEY `si_title` (`si_title`),
  FULLTEXT KEY `si_text` (`si_text`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=1
1 row in set (0.00 sec)

Regards --Rabe 20:21, 4 December 2011 (UTC)

The fulltext index type is only available on MyISAM; by default, all MediaWiki tables *except* 'searchindex' are InnoDB, and 'searchindex' as an exception gets MyISAM specifically so it will work.

This leaves it as a single point of failure in an InnoDB system, where this table can still get marked crashed and cause annoyance.

HOWEVER! If you use a separate search engine instead, you don't actually need the searchindex table's fulltext index. Try setting up Sphinx or Lucene search backends, and you can drop the fulltext indexes. --brion 23:04, 4 December 2011 (UTC)

In the meantime, InnoDB from MySQL 5.6 onwards now got full text indeces. Once the MySQL requirement of MediaWiki got raised to MySQL 5.6 or higher, we can get rid of this last MyISAM table. --87.123.36.114 15:48, 19 February 2016 (UTC)

Can I convert it to InnoDB if I'm already using MySQL 5.6? --Ranmin (talk) 03:55, 9 July 2016 (UTC)

Brion, what do you mean by "This leaves it as a single point of failure in an InnoDB system, where this table can still get marked crashed and cause annoyance"? Why/When would it crash? I am having the searchindex table being recurrently marked as crashed on an InnoDB system (except for the searchindex table, which is MyISAM, exactly as you mentioned). --Rbirmann (talk) 19:13, 19 September 2016 (UTC)

Doc about indexes seems to be wrong

 mysql> show indexes from searchindex;
 +-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 | Table       | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
 +-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 | searchindex |          0 | si_page  |            1 | si_page     | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
 | searchindex |          1 | si_title |            1 | si_title    | NULL      |        NULL |     NULL | NULL   |      | FULLTEXT   |         |               |
 | searchindex |          1 | si_text  |            1 | si_text     | NULL      |        NULL |     NULL | NULL   |      | FULLTEXT   |         |               |
 +-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 3 rows in set (0.02 sec)

whereas this page shows only si_page and si_text. Someone less chickennewbie than me to correct it, after some verification?

Important note: I did not install MediaWiki, but used the mwdumper.jar program to import a dump of Wikipédia. However this should not change the indexes layout. --MathsPoetry (talk) 08:44, 25 February 2013 (UTC)

You miss si_title; in the meantime it has been added to the page. --88.130.93.218 19:39, 5 March 2014 (UTC)