dongsong73032
2013-09-21 19:31
浏览 7
已采纳

如何加快或优化数据库查询?

I'm somewhat new to mySQL and PHP. I have a little program that looks up a text's words in an etymological dictionary. -- Source here on github. It can only look up 1-3 words per second, which is a real limitation, especially when I'm trying to analyze a text that's bigger than a thousand words. Is there a way I can better structure my queries or database so that I can speed up this process?

Function that looks up a word:

function lookup($word) { 
    //connect to database
    $query="SELECT parent_lang FROM etym_dict WHERE word=\"$word\" and word_lang=\"eng\""; //making this English-only for now
    //debug_print("<p>Query is: $query</p>"); 
    $result=dbquery($query) 
    or die("Failed to look up words in database."); 
    $parent_lang=mysqli_fetch_array($result); 
    $parent_lang=$parent_lang[0]; 
    return $parent_lang; 
} 

Thing that calls that function:

foreach (array_keys($results) as $word) { 
    $parent_lang=lookup($word); 
    if (!empty($parent_lang)) {  
        $parent_langs[]=array($word,$parent_lang,$results[$word]); 
        debug_print("$word, "); 
    } else { 
        $derivation=lookup_derivation($word); 
        $has_derivation= (strlen($derivation)>0) ? TRUE : FALSE; 
        if ($has_derivation) { 
            $parent_lang=lookup($derivation); 
        } 
        if(!empty($parent_lang) && $has_derivation) { 
            debug_print("<span class=\"blue\">$word ($derivation)</span>, "); 
        } else if(!empty($parent_lang)) { 
            $parent_langs[]=array($word,$parent_lang,$results[$word]); 
            debug_print("<span class=\"blue\">$word</span>, "); 
        } else { 
            $not_in_dict[]=$word; 
            debug_print("<span class=\"red\">$word"); 
            if ($has_derivation) { 
                debug_print("/$derivation</span>, "); 
            } else { 
                debug_print("</span>, "); 
            } 
        } 
    } 
} 

Db query function:

 function dbquery($sql) {
                GLOBAL $dbc;
                $result=mysqli_query($dbc,$sql);
                return $result;
        }

Db connect function:

function dbconnect() {
            $dbc=mysqli_connect(
                  ... // redacted 
            ) or die ('Error connecting to database.');
            return $dbc;
    }

Sample etym_dict dataDefinitions

  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • duanmuybrtg1231 2013-09-21 20:33
    已采纳

    My first guess is that you don't have indexes on etym_dict, but that's only a guess because you haven't shown us the table definitions.

    已采纳该答案
    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题