View Issue Details

IDProjectCategoryView StatusLast Update
0000091XMB1New Featurespublic2011-11-08 17:52
Reportermiqrogroove Assigned To 
Status acknowledgedResolutionsuspended 
Summary0000091: Implement Full-Text Indexing
DescriptionI just realized XMB is using table scans to perform searches. D:

Additional InformationTypical result of EXPLAIN

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE p ALL tid NULL NULL NULL 10 Using where; Using temporary; Using filesort
1 SIMPLE f eq_ref PRIMARY PRIMARY 2 t.fid 1

See Also
TagsNo tags attached.
MySQL Version
PHP Version
Web Server
Original Reporter
SVN Revision



2008-08-01 14:40

administrator   ~0000009

If we want to keep the table scan mode for short words then modes can be switched using the result of...

SHOW variables WHERE variable_name = 'ft_min_word_len'


2008-11-16 19:35

administrator   ~0000066

More about our problem and why changes are needed:

The existing XMB search feature, which is based on a table scanning algorithm, appears to run quickly due to the use of a LIMIT clause. However, search performance falls off dramatically when searching for a more-rare keyword. As the prevalence of the keyword decreases, the portion of the xmb_posts table that must be scanned increases. This creates a situation where searching for a keyword that cannot be found (a long random string) causes the database engine to scan the body of every post in the database.

A full-text indexing strategy would give us the opposite result: Searching for a keyword that could not be found would result in the fastest possible SQL processing time of any search. This is because the database engine would be able to return an empty set from the index without even touching the table.


2008-11-22 16:05

administrator   ~0000074

Due to complications with the old upgrade.php utility, this feature will not be practical in the next version. Leaving open as a candidate for any future releases.

Issue History

Date Modified Username Field Change
2008-08-01 14:35 miqrogroove New Issue
2008-08-01 14:36 miqrogroove Relationship added child of 0000054
2008-08-01 14:40 miqrogroove Note Added: 0000009
2008-11-16 19:35 miqrogroove Note Added: 0000066
2008-11-22 16:05 miqrogroove Note Added: 0000074
2008-11-22 16:05 miqrogroove Status new => acknowledged
2008-11-22 16:05 miqrogroove Resolution open => suspended
2008-11-22 16:05 miqrogroove Projection none => major rework
2008-11-22 16:05 miqrogroove Target Version 1.9.11 =>
2008-11-22 16:05 miqrogroove Additional Information Updated
2008-11-22 16:08 miqrogroove Relationship deleted child of 0000054