SQL语句如何优化,这样的搜索请求, 给个思路。指点一二

kstsca 发布于 2012/07/04 14:37
阅读 1K+
收藏 1
/*获取筛选属性*/
$attr_para = $_GET['para']; 		//形状
$attr_cut = $_GET['cut']; 			//切工
$attr_color = $_GET['color']; 		//颜色
$attr_symmetry = $_GET['symmetry'];	//对称
$attr_clarity = $_GET['clarity'];	//净度
$attr_polish = $_GET['polish'];		//抛光
$attr_cert = $_GET['cert'];		    //证书
$attr_location = $_GET['location'];		//所在地

 /* 属性筛选 */
	$attr_r = '';	//页面输出筛选条件
	if($attr_cut){
		$attr_str='';
		$cut_where='';
		foreach($attr_cut as $key=>$value){
			if($key==0){$attr_str.='\''.$value.'\'';}
			else{$attr_str.=',\''.$value.'\'';}	
			$temp = 'cut_'.$value;
			$$temp = 1;
			$smarty->assign("$temp",      $$temp);  // 输出已筛选条件
		}
		$cut_where = ' AND attr_value in('.$attr_str.')';
		$attr_r .= '切工:'.$attr_str.' ';
	}


	$sql = 'SELECT g.goods_id,g.goods_name,g.goods_sn,g.market_price,g.shop_price,g.promote_price,g.promote_start_date,g.promote_end_date';
	
	$sql.= ',cut';
	$sql.= ',color';
	$sql.= ',symmetry';
	$sql.= ',clarity';
	$sql.= ',polish';
	$sql.= ',cert';
	$sql.= ',carat';
	$sql.= ',location';
	$sql.= ',certificate';
	
	$sql_f.= ' FROM ' . $GLOBALS['ecs']->table('goods') .' AS g ';
	
	$sql_f.= ',(SELECT goods_id, attr_value AS cut FROM ' . $GLOBALS['ecs']->table('goods_attr') .' WHERE attr_id=2'.$cut_where.') AS cut';
	$sql_f.= ',(SELECT goods_id, attr_value AS color FROM ' . $GLOBALS['ecs']->table('goods_attr') .' WHERE attr_id=3'.$color_where.') AS color';
	$sql_f.= ',(SELECT goods_id, attr_value AS symmetry FROM ' . $GLOBALS['ecs']->table('goods_attr') .' WHERE attr_id=6'.$symmetry_where.') AS symmetry';
	$sql_f.= ',(SELECT goods_id, attr_value AS clarity FROM ' . $GLOBALS['ecs']->table('goods_attr') .' WHERE attr_id=4'.$clarity_where.') AS clarity';
	$sql_f.= ',(SELECT goods_id, attr_value AS polish FROM ' . $GLOBALS['ecs']->table('goods_attr') .' WHERE attr_id=5'.$polish_where.') AS polish';
	$sql_f.= ',(SELECT goods_id, attr_value AS cert FROM ' . $GLOBALS['ecs']->table('goods_attr') .' WHERE attr_id=8'.$cert_where.') AS cert';
	$sql_f.= ',(SELECT goods_id, attr_value AS carat FROM ' . $GLOBALS['ecs']->table('goods_attr') .' WHERE attr_id=1'.$carat_where.') AS carat';
	$sql_f.= ',(SELECT goods_id, attr_value AS location FROM ' . $GLOBALS['ecs']->table('goods_attr') .' WHERE attr_id=62) AS location';
	$sql_f.= ',(SELECT goods_id, attr_value AS certificate FROM ' . $GLOBALS['ecs']->table('goods_attr') .' WHERE attr_id=8) AS certificate';
	
	$sql_f.= ' WHERE g.goods_id = cut.goods_id';	
	$sql_f.= ' AND g.goods_id = color.goods_id';	
	$sql_f.= ' AND g.goods_id = symmetry.goods_id';	
	$sql_f.= ' AND g.goods_id = clarity.goods_id';	
	$sql_f.= ' AND g.goods_id = polish.goods_id';
	$sql_f.= ' AND g.goods_id = cert.goods_id';
	$sql_f.= ' AND g.goods_id = carat.goods_id';	
	$sql_f.= ' AND g.goods_id = location.goods_id';	
	$sql_f.= ' AND g.goods_id = certificate.goods_id';

瓶颈就在shop_goods_attr 表联询9次,帮我看看,

涉及shop_goods与shop_goods_attr两张表,

shop_goods_attr列

如代码如示attr_id=2是切工 attr_id=3是颜色,以此列推。。。

那位指点几下如何优化这条SQL

加载中
0
纠结名字_我艹你妹
纠结名字_我艹你妹
我也是这样做的  同求!
0
黄龍
黄龍

很想帮你但是我看到PHP就头疼,你可以直接把sql分离出来吗~?

黄龍
黄龍
回复 @kstsca : 我不是学PHP的~要花时间去看你PHP代码就太浪费了~我意思是你吧最后拼出来的SQL代码打印出来!
kstsca
kstsca
所以出现了上面的 用in查询
0
kstsca
kstsca
主表goods 商品表
附表arr 商品属性表

譬如

goods

id    name

1     大神

arr表

id  goodid(商品id)   arrid(商品属性id)   value(值)

1    1                       1                         ex

2    1                        2                         ex

3    1                        3                           ex

 

商品属性ID 1是切工  2是光泽  3是材质

 

现在我要查  切工ex   材质idea  的产品

0
kstsca
kstsca
可能还是涉及到 切工 ex  idea 多值的查询
0
RickyFeng
RickyFeng

试试这样做会不会比你的快.

  $sql_f.= ',(SELECT goods_id, attr_value AS cut FROM ' . $GLOBALS['ecs']->table('goods_attr') .
  ' WHERE (attr_id=2'.$cut_where . ') or '.
         '(attr_id=3'.$color_where. ') or '.
         '(attr_id=6'.$symmetry_where  . ') or '.
         ......
         ') AS new_table_name';

kstsca
kstsca
要注意的是每次连表都有一个as
kstsca
kstsca
这效率又回老路了,感觉
kstsca
kstsca
这样好像缺一步,处理成新表后,如何再输出各商品值呢?
kstsca
kstsca
好的
0
齐迹
齐迹
代码看上去很熟悉,原来是ecshop
mark35
mark35
回复 @kstsca : 看表名,字段名就知道了
mark35
mark35
回复 @kstsca : 肯定是ecs啦
kstsca
kstsca
这段代码应该不是ecshop写的,你看出来应该是表名吧
0
酒逍遥
酒逍遥
头大...表设计的有问题吧
kstsca
kstsca
这样设计很常规吧,应该把请求让php处理好,再写sql,但现在为了省时,想直接改sql优化
0
RickyFeng
RickyFeng

引用来自“RickyFeng”的答案

试试这样做会不会比你的快.

  $sql_f.= ',(SELECT goods_id, attr_value AS cut FROM ' . $GLOBALS['ecs']->table('goods_attr') .
  ' WHERE (attr_id=2'.$cut_where . ') or '.
         '(attr_id=3'.$color_where. ') or '.
         '(attr_id=6'.$symmetry_where  . ') or '.
         ......
         ') AS new_table_name';

改为以下方式:

SELECT goods_id, attr_value AS cut FROM ' . $GLOBALS['ecs']->table('goods_attr') .' WHERE attr_id=2'.$cut_where.'
UNION ALL
SELECT goods_id, attr_value AS color FROM ' . $GLOBALS['ecs']->table('goods_attr') .' WHERE attr_id=3'.$color_where.'
.......

kstsca
kstsca
还是谢谢热心关注我的提问。。
kstsca
kstsca
感觉上去 效率应该差不多的,呵呵,回去试试看
0
kstsca
kstsca
继续呼唤sql牛人
0
宏哥
宏哥

终极解决方法:

1:把所有数据弄到一张表里面 --- 不冗余,不是mysql, mysql根本不是数据库

2:换数据库 

mark35
mark35
回复 @HelloChina : 总比浪费生命时光好。垃圾的mysql不支持交集运算,只能把数据取出来在应用层做集合运算。想着就蛋痛。
HelloChina
HelloChina
又看到宏哥 在让人还数据库了
宏哥
宏哥
回复 @kstsca : 坦率的说,这个问题无解. 你的sql 并没有什么问题. 问题在于mysql缺乏集合运算能力.
kstsca
kstsca
这方法太耗时了,再说一个布怎么重要客户的,没这么大费周章,现在想让那位sql牛人看看,是否优化下sql语句
返回顶部
顶部