#1 Caching word lookup? Add Index to words table.

open
nobody
None
5
2008-10-02
2008-10-02
Anonymous
No

Hello, when doing a search which will return many results, there will be an insane number of word lookups. This slows things down quite a bit.

SUGGESTION 1 (cache word lookups):
This is caused in _search.php's hashToText method.
Specifically you do:
$word = sql_fetch("SELECT word FROM `words` WHERE id=$num");

if you do something like this you can store the words in a global so you only have to do the lookup if you need too.
function hashToText($content, $boldMiddle = false) {
global $cachedWord;
if (!isset($cachedWord)) $cachedWord = array();

$pairs = str_split($content, $CRAWL_CHARS_PER_WORD);
$text = "";
$i = 0;
foreach ($pairs as $pair) {
$num = toDecimal($pair);

// Was the word cached?
if (!isset($cachedWord[$num]))
{
// Word was not in cache, we need to fetch it from the database.
$word = sql_fetch("SELECT word FROM `words` WHERE id=$num");
$cachedWord[$num] = $word;
}
else
{
// Found the word in our cache!
$word = $cachedWord[$num];
}

.........etc

SUGGESTION 2 (add id index to words table):
Also the 'words' table should have a index on on the id field to speed lookups when a lookup must be made.
This insures that if the lookup has to goto the database then it will be quick.

Discussion