select count(*)
from tbl_alarmlog a
where ((a.dev_type=75)
or ((select id from tbl_userdev where a.dev_id=dev_id and a.dev_type=dev_type and user_id=4) is not null)
or (a.dev_type=4 and (select id from tbl_userdev where dev_type=9 and user_id=4 and dev_id in (select a_id from tbl_point where id=a.dev_id)) is not null)
or (a.dev_type=7 and a.serverid=0 and (select id from tbl_patroluser where a.dev_id=ep_id and userid=4 or userid in (select id from tbl_user where creater_id=4)) is not null)
or (a.dev_type=7 and a.serverid<>0)
or ((a.dev_type=1 or a.dev_type=128) and (select id from tbl_userdev where user_id=4 and dev_type=64 and dev_no=a.puid and dev_id=a.channel) is not null))
and a.alarm_time>=FROM_UNIXTIME(1330531200) and a.alarm_time<FROM_UNIXTIME(1363190399) and a.serverid=0请问如何把上述的or语句改成and,貌似说查询子句中or语句会导致全表搜索。
或者说如何优化上述语句,哪些地方要加索引,哪些语句可以优化的?
from tbl_alarmlog a
where ((a.dev_type=75)
or ((select id from tbl_userdev where a.dev_id=dev_id and a.dev_type=dev_type and user_id=4) is not null)
or (a.dev_type=4 and (select id from tbl_userdev where dev_type=9 and user_id=4 and dev_id in (select a_id from tbl_point where id=a.dev_id)) is not null)
or (a.dev_type=7 and a.serverid=0 and (select id from tbl_patroluser where a.dev_id=ep_id and userid=4 or userid in (select id from tbl_user where creater_id=4)) is not null)
or (a.dev_type=7 and a.serverid<>0)
or ((a.dev_type=1 or a.dev_type=128) and (select id from tbl_userdev where user_id=4 and dev_type=64 and dev_no=a.puid and dev_id=a.channel) is not null))
and a.alarm_time>=FROM_UNIXTIME(1330531200) and a.alarm_time<FROM_UNIXTIME(1363190399) and a.serverid=0请问如何把上述的or语句改成and,貌似说查询子句中or语句会导致全表搜索。
或者说如何优化上述语句,哪些地方要加索引,哪些语句可以优化的?
select count(*)
from (SELECT 1 FROM tbl_alarmlog a where a.dev_type=75
UNION ALL
SELECT 1 FROM tbl_alarmlog a INNER JOIN tbl_userdev B
ON a.dev_id=B.dev_id and a.dev_type=B.dev_type and B.user_id=4
UNION ALL
...
)
(
productCode VARCHAR,
productColor VARCHAR
)
BEGIN
DECLARE con INT;
SELECT COUNT(1) INTO con FROM yt_pro_productcolor WHERE proCode=productCode;
IF con>0 THEN
UPDATE yt_pro_productcolor SET proColor=productColor WHERE proCode=productCode;
ELSE
BEGIN
DECLARE productid VARCHAR(20);
SELECT id INTO @productid FROM yt_pro_productinfo WHERE proCode=productCode;
INSERT INTO yt_pro_productcolor(proId,proCode,proColor)VALUES(productid,productCode,productColor);
END;
END IF;
END
存储过程不能创建,求高手解决: