有人能帮我解决吗
iam使用条令运行查询。
我为必要的表编制了索引并提供了参考
这是我的查询
echo "step1";echo date('h:i:s A');echo "<br/>";
$query = new Doctrine_Query();
$whereCond ='';
$where='eng.id='.$campid;
$select='';
$search = '';
/*if($queryv!=''){
$where .= " AND c.".$quickSearchType." LIKE '%".$queryv."%' ";
}*/
if($letterPressed == 'All')
{
$where .="";
}
else if($letterPressed == 'Radian6')
{
$where .=" AND c.news_type = 1";
}
else if($letterPressed == 'Google News')
{
$where .=" AND c.news_type = 3";
}
else if($letterPressed == 'Google Blogs')
{
$where .=" AND c.news_type = 4";
}
else if($letterPressed == 'RSS Feeds')
{
$where .=" AND (c.news_type = 5 or c.news_type = 6)";
}
else if($letterPressed == 'Hide Twitter')
{
$where .=" AND c.url NOT LIKE '%twitter.com%'";
}
else if($letterPressed == 'Hide Facebook')
{
$where .=" AND c.url NOT LIKE '%facebook.com%'";
}
else if($letterPressed == 'Hide Facebook-twitter')
{
$where .=" AND (c.url NOT LIKE '%twitter.com%' AND c.url NOT LIKE '%facebook.com%')";
}
else if($letterPressed == 'Show Twitter')
{
$where .=" AND c.url LIKE '%twitter.com%'";
}
else if($letterPressed == 'Show Facebook')
{
$where .=" AND c.url LIKE '%facebook.com%'";
}
else if($letterPressed == 'show Facebook-twitter')
{
$where .=" AND (c.url LIKE '%twitter.com%' OR c.url LIKE '%facebook.com%')";
}
if($queryv!='' && $quickSearchType == 'title' && (trim($letterPressed) != 'Law' && trim($letterPressed) != 'LS Translate') ){
$search = ' LOWER(c.title) LIKE "%'.mysql_real_escape_string($queryv).'%" ';
//$search = ' LOWER(c.title) LIKE ?';
}
if($queryv!='' && $quickSearchType == 'domain')
{
$search = ' LOWER(c.domain) LIKE "%'.mysql_real_escape_string($queryv).'%" ';
//$search = ' LOWER(c.domain) LIKE ?';
}
echo "step2";echo date('h:i:s A');echo "<br/>";
$select='c.id, c.campaign_id, c.title as title, c.domain, c.news_type,c.url, c.active, c.article_date, c.created_date, c.modified_date, t.language_id, t.tr_translated_date, t.tr_translated_by, t.translation_state, t.tr_title, t.raw_title';
if(trim($letterPressed) == 'Original')
{
$select='c.id, c.campaign_id, c.title as title, c.domain, c.news_type,c.url, c.active, c.article_date, c.created_date, c.modified_date, t.language_id, t.tr_translated_date, t.tr_translated_by, t.translation_state';
}
if(trim($letterPressed) == 'Law')
{
$select='c.id, c.campaign_id, c.domain, c.news_type,c.url, c.active,c.article_date, c.created_date, c.modified_date, t.raw_title as title, t.language_id, t.tr_translated_date, t.tr_translated_by, t.translation_state';
if($queryv!='' && $quickSearchType == 'title'){
$search = ' LOWER(t.raw_title) LIKE "%'.mysql_real_escape_string($queryv).'%"';
//$search = ' LOWER(t.raw_title) LIKE ?';
}
}
if(trim($letterPressed) == 'LS Translate')
{
$select='c.id, c.campaign_id, c.domain, c.news_type,c.url, c.active, c.article_date, c.created_date, c.modified_date, t.tr_title as title, t.language_id, t.tr_translated_date, t.tr_translated_by, t.translation_state';
$where .=" AND t.translation_state = 1";
if($queryv!='' && $quickSearchType == 'title'){
$search = ' LOWER(t.tr_title) LIKE "%'.mysql_real_escape_string($queryv).'%" ';
//$search = ' LOWER(t.tr_title) LIKE ?';
}
}
$whereCond.=$where;
// , array("%".mysql_real_escape_string($queryv)."%")
echo "step3";echo date('h:i:s A');echo "<br/>";
$query->select($select)
->from('News c')
->where($whereCond)
->addWhere('eng.tr_language_id=t.language_id')
->andWhere('c.created_date >= DATE_SUB(CURDATE(),INTERVAL 90 DAY)' );
if(!empty($search))
$query->andWhere($search);
$query->leftJoin('c.TranslatedNews t on t.news_id = c.id')
->leftJoin('c.Campaigns eng on eng.id = c.campaign_id')
->orderBy('c.'.$sortName. ' ' . $sortOrder)
->groupBy('c.title')
->addGroupBy('c.url')
->limit(10);
echo "step4";echo date('h:i:s A');echo "<br/>";
//echo $query->getSqlQuery();exit;
$result = $query->execute(array(),Doctrine::HYDRATE_ARRAY);
echo "step5";echo date('h:i:s A');echo "<br/>";exit;
return $result;
执行查询需要8秒。如何使此查询运行快速
提前感谢
因为LIKE在表增长时速度较慢。
您应该实现Full Text搜索
所以你可以做
WHERE MATCH(fields) AGAINST('your search')
当您将通配符book-end放在%search-term%上时，LIKE比较实际上是一个"包含子字符串"搜索。排序索引的典型优势在这样的搜索中是不可用的（因为通配符表示忽略值开头的字符）；因此，必须检查表中（相关列中）的每个值，看它是否包含子字符串。
如果你在电话簿上搜索WHERE SURNAME LIKE'%els%'，你必须检查每个姓氏，而如果你搜索WHERE SURNAMELIKE'Nels%，你只能搜索-N个名字，不包括以任何其他字母开头的名字。后者可以使用索引。
数据库的创建者可以通过从url中提取域并将域放在自己的列中并对该列进行索引来帮助您。然后，您可以进行相等比较，WHERE DOMAIN="somedomain"，并且可以非常快速有效地找到值。