Mysql函数求优化
小弟刚开始接触Mysql数据库,需要基于Mysql数据库做报表开发,写的一个函数查询时候效率特别低下。
备注:传进来比较的参数数据量 5W条左右。请高手指教优化方法。
另外,一般Mysql调试的技巧及工具都用什么
函数如下:
DELIMITER $$DROP FUNCTION IF EXISTS `nqrptr`.`get_ip_type_test`$$CREATE DEFINER=`netqos`@`%` FUNCTION `get_ip_type_test`(ip_no bigint(10),code_type int(10), return_type varchar(20)) RETURNS varchar(40) CHARSET latin1BEGIN DECLARE return_value varchar(30); DECLARE temp_prov varchar(30); DECLARE temp_city varchar(30); DECLARE temp_busi varchar(30); DECLARE iftrue int(10); select count(1) into iftrue from nqrptr.idc_ip_mapping where status = 1 AND type_id = code_type AND start_ip_no <= ip_no AND end_ip_no >= ip_no; if iftrue = 0 then set return_value = 'Unkown Ip'; else SELECT provinces_id,city_id,busi_id INTO temp_prov,temp_city,temp_busi FROM nqrptr.idc_ip_mapping WHERE status = 1 AND type_id = code_type AND start_ip_no <= ip_no AND end_ip_no >= ip_no; if return_type = 'PRO' then set return_value = temp_prov; elseif return_type = 'CITY' then set return_value = temp_city; elseif return_type = 'BUSI' then set return_value = temp_busi; elseif return_type = 'IPFromTo' then set return_value = concat(temp_start,' to ',temp_end); else set return_value='other'; end if; end if; RETURN return_value; END$$DELIMITER ;
DELIMITER $$DROP FUNCTION IF EXISTS `nqrptr`.`get_ip_type_test`$$CREATE DEFINER=`netqos`@`%` FUNCTION `get_ip_type_test`(ip_no bigint(10),code_type int(10), return_type varchar(20)) RETURNS varchar(40) CHARSET latin1BEGIN DECLARE return_value varchar(30); DECLARE temp_prov varchar(30); DECLARE temp_city varchar(30); DECLARE temp_busi varchar(30); DECLARE iftrue int(10); SELECT provinces_id,city_id,busi_id INTO temp_prov,temp_city,temp_busi FROM nqrptr.idc_ip_mappingWHERE status = 1 AND type_id = code_type AND start_ip_no <= ip_no AND end_ip_no >= ip_no; if FOUND_ROWS() = 0 then set return_value = 'Unkown Ip'; else if return_type = 'PRO' then set return_value = temp_prov; elseif return_type = 'CITY' then set return_value = temp_city; elseif return_type = 'BUSI' then set return_value = temp_busi; elseif return_type = 'IPFromTo' then set return_value = concat(temp_start,' to ',temp_end); else set return_value='other'; end if; end if; RETURN return_value; END$$DELIMITER ;