View source for Manual:Searchindex table

<languages />
{{Database layout}}
{{TOCright}}
<translate><!--T:1--> The <tvar name=1>'''searchindex'''</tvar> table is used to provide full text <tvar name=search>{{ll|search|searches}}</tvar>.</translate>
<translate><!--T:2--> Before MySQL 5.6, full text indexes were only provided by the MyISAM table engine, but not by InnoDB.</translate>
<translate><!--T:3--> The text table (cur table in 1.4 and earlier) however uses the InnoDB type to improve concurrency, so a copy of the current page text was required in order to make use of these indexes.</translate>
<translate><!--T:4--> This is the reason why this table is there.</translate>
<translate><!--T:5--> If using Postgres, this table does not exist: the full text information is stored as columns in the <tvar name=1>{{db table|page}}</tvar> and <tvar name=2>{{db table|pagecontent}}</tvar> tables directly.</translate>

<translate><!--T:26--> This table is populated when edits are saved on pages, unless <tvar name=1>{{wg|DisableSearchUpdate}}</tvar> is set to <tvar name=2>{{phpi|true}}</tvar>, or an extension that implements its own search index (like <tvar name=3>{{ll|Extension:CirrusSearch|CirrusSearch}}</tvar> extension) is installed.</translate>
<translate><!--T:27--> The <tvar name=1>{{ll|Manual:rebuildtextindex.php|rebuildtextindex.php}}</tvar> maintenance script can be used to populate it from scratch, or <tvar name=2>{{ll|Manual:updateSearchIndex.php|updateSearchIndex.php}}</tvar> to update it for recently changed pages.</translate>

<translate>
==Fields== <!--T:6-->
</translate>

===si_page===
<translate><!--T:7--> Key to <tvar name=page_id>{{ll|Manual:Page table#page_id|page_id}}</tvar>.</translate>

===si_title===
<translate><!--T:8--> Munged version of page title.</translate>

===si_text===
<translate>
<!--T:9-->
Munged version of the current text of the page.

== Details == <!--T:25-->

<!--T:15-->
Indexes for this, using en wikipedia for cardinality:
</translate>
<pre style="overflow-x: scroll; white-space: pre;">
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table       | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| searchindex |          0 | si_page  |            1 | si_page     | A         |      797275 |     NULL | NULL   |      | BTREE      |         |
| searchindex |          1 | si_title |            1 | si_title    | A         |      265758 |     NULL | NULL   |      | FULLTEXT   |         |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
</pre>

<translate><!--T:16--> A common search query is of the form:</translate>

<syntaxhighlight lang=sql>
SELECT page_id, page_namespace, page_title FROM `page`,`searchindex`
WHERE page_id=si_page
AND MATCH(si_text) AGAINST('+''searchterm''' IN BOOLEAN MODE)
AND page_is_redirect=0
AND page_namespace IN (0)
LIMIT 20
</syntaxhighlight>

<translate><!--T:17--> In MediaWiki version 1.4 and earlier:</translate>

<syntaxhighlight lang=sql>
SELECT cur_id,cur_namespace,cur_title,cur_text FROM cur,searchindex
WHERE cur_id=si_page
AND (
  MATCH(si_text) AGAINST('+''searchterm''' IN BOOLEAN MODE)
  AND cur_is_redirect=0
)
AND cur_namespace IN (0,9,11)
LIMIT 0, 20;
</syntaxhighlight>

<translate><!--T:18--> Explain shows the following:</translate>
<pre>
+-------------+--------+-------------------------------------------+---------------+---------+------------+--------+-------------+
| table       | type   | possible_keys                             | key           | key_len | ref        | rows   | Extra       |
+-------------+--------+-------------------------------------------+---------------+---------+------------+--------+-------------+
| cur         | range  | cur_id,cur_namespace,name_title_timestamp | cur_namespace |       1 | NULL       | 317499 | Using where |
| searchindex | eq_ref | si_page                                   | si_page       |       4 | cur.cur_id |      1 | Using where |
+-------------+--------+-------------------------------------------+---------------+---------+------------+--------+-------------+
</pre>

<translate>
==Known problems== <!--T:19-->
</translate>
<translate><!--T:20--> A range scan with namespaces containing a large number of records requires that a large portion of the searchindex table be loaded into RAM.</translate>
<translate><!--T:21--> This is very costly, in part because the searchindex table contains the full text of the article.</translate>
<translate><!--T:22--> A long term plan is to remove the text from the searchindex table.</translate>
<translate><!--T:23--> Temporary measures may involve adding fields to searchindex and indexing them (and using a self join to allow both that index and the fulltext index to be used) or adding a searchindex index to decrease the number of complete searchindex records which must be read to match the query.</translate>

<translate>
==Schema summary== <!--T:24-->
</translate>
{{MW 1.10|and after}}<!--as of 1.32-->
{{describe table}}
<pre>
+----------+------------------+------+-----+---------+-------+
| Field    | Type             | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| si_page  | int(10) unsigned | NO   | PRI | NULL    |       |
| si_title | varchar(255)     | NO   | MUL |         |       |
| si_text  | mediumtext       | NO   | MUL | NULL    |       |
+----------+------------------+------+-----+---------+-------+
</pre>

{{MW version|version=1.1|version2=1.9}}
{{describe table}}
<pre>
+----------+-----------------+------+-----+---------+-------+
| Field    | Type            | Null | Key | Default | Extra |
+----------+-----------------+------+-----+---------+-------+
| si_page  | int(8) unsigned | NO   | PRI | NULL    |       |
| si_title | varchar(255)    | NO   | MUL | NULL    |       |
| si_text  | mediumtext      | NO   | MUL | NULL    |       |
+----------+-----------------+------+-----+---------+-------+
</pre>

<translate>
==Indexes== <!--T:28-->
</translate>
{{MW 1.4|and after}}<!--Still valid as of 1.37-->
{{show index in table}}
<pre style="overflow-x: scroll; white-space: pre;">
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| 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      |           0 |     NULL | NULL   |      | FULLTEXT   |         |               |
| searchindex |          1 | si_text  |            1 | si_text     | NULL      |           0 |     NULL | NULL   |      | FULLTEXT   |         |               |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
</pre>

{{databases}}

[[Category:MediaWiki database tables{{#translation:}}|Searchindex table]]
[[Category:Search{{#translation:}}|Searchindex table]]