View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0000091 | XMB1 | New Features | public | 2008-08-01 14:35 | 2011-11-08 17:52 |
| Reporter | miqrogroove | Assigned To | |||
| Priority | normal | Severity | feature | Reproducibility | N/A |
| Status | acknowledged | Resolution | suspended | ||
| Summary | 0000091: Implement Full-Text Indexing | ||||
| Description | I just realized XMB is using table scans to perform searches. D: | ||||
| Additional Information | Typical 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 t ALL PRIMARY NULL NULL NULL 4 Using where 1 SIMPLE f eq_ref PRIMARY PRIMARY 2 t.fid 1 See Also http://forums.xmbforum.com/viewthread.php?tid=773516 | ||||
| Tags | No tags attached. | ||||
| MySQL Version | |||||
| PHP Version | |||||
| Web Server | |||||
| Browser | |||||
| Flags | |||||
| Original Reporter | |||||
| SVN Revision | |||||
| Git Commit | |||||
|
|
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' |
|
|
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. |
|
|
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. |
| 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 |
