大哥帮忙优化一下mysql数据库的索引，谢谢

[wangkunhong]

大哥您好，请帮忙优化一下索引好吗？谢谢了！
简历表：

-- Table "perresume" DDL

CREATE TABLE `perresume` (
  `ID` int(4) NOT NULL auto_increment,
  `regname` int(12) default NULL,
  `post` varchar(60) character set gbk default NULL,
  `myname` varchar(20) character set gbk default NULL,
  `mysex` int(1) default NULL,
  `tel` varchar(50) character set gbk default NULL,
  `xueli` int(1) default '0',
  `born_year` varchar(15) default NULL,
  `cm` char(3) character set gbk default NULL,
  `place` int(1) default NULL,
  `JobMainClass` int(4) default NULL,
  `JobSubClass` char(4) character set gbk default NULL,
  `WorkYear` int(1) default NULL,
  `Salary` int(2) unsigned default NULL,
  `CardType` int(1) default NULL,
  `IDNumber` varchar(25) character set gbk default NULL,
  `schoolname` varchar(100) character set gbk default NULL,
  `pro_a` int(8) default '0',
  `pro_b` int(8) default '0',
  `industry` int(4) default '0',
  `Statu` int(4) default '0',
  `Status` char(4) default NULL,
  `mobile` varchar(15) default NULL,
  `sendmobile` int(1) default '1',
  `email` varchar(50) default NULL,
  `homepage` varchar(80) character set gbk default NULL,
  `oicq` varchar(25) character set gbk default NULL,
  `addr` varchar(250) default NULL,
  `__mpost` char(6) character set gbk default NULL,
  `Comput` varchar(50) character set gbk default NULL,
  `First1` varchar(50) character set gbk default NULL,
  `First1value` varchar(50) character set gbk default NULL,
  `First2` varchar(50) character set gbk default NULL,
  `First2value` varchar(50) character set gbk default NULL,
  `First3` varchar(50) character set gbk default NULL,
  `First4` varchar(50) character set gbk default NULL,
  `image` varchar(50) character set gbk default NULL,
  `noviewcom1` varchar(45) character set gbk default NULL,
  `noviewcom2` varchar(45) character set gbk default NULL,
  `noviewcom3` varchar(45) character set gbk default NULL,
  `resume` mediumtext,
  `sendtime` datetime default '0000-00-00 00:00:00',
  `setShowNum` int(8) default '1',
  `showstate` int(1) default NULL,
  `flagshow` int(1) default '0',
  PRIMARY KEY  (`ID`),
  KEY `adv_search_jobclass` (`JobMainClass`,`JobSubClass`,`sendtime`),
  KEY `adv_search` (`pro_b`,`JobMainClass`,`JobSubClass`,`sendtime`),
  KEY `sendtime` (`sendtime`),
  KEY `jobclass` (`JobMainClass`,`JobSubClass`),
  KEY `regname` (`regname`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='InnoDB free: 66560 kB; InnoDB free: 68608 kB; InnoDB free: 8';

企业查询简历的条件保有存表：

-- Table "com_search_table" DDL

CREATE TABLE `com_search_table` (
  `ID` int(10) NOT NULL auto_increment,
  `cost_comId` int(10) default NULL,
  `cost_sex` int(1) default NULL,
  `cost_edu` varchar(45) default NULL,
  `cost_pro_script` varchar(45) default NULL,
  `cost_pro_value` varchar(45) default NULL,
  `cost_location` varchar(45) default NULL,
  `cost_work_start` int(1) default NULL,
  `cost_work_end` int(1) default NULL,
  `cost_is_photo` int(1) default NULL,
  `cost_job_script` varchar(45) default NULL,
  `cost_job_value` varchar(45) default NULL,
  `cost_job_place` varchar(45) default NULL,
  `cost_keyword` varchar(45) default NULL,
  `cost_resume_time` int(5) default NULL,
  `cost_create_date` datetime default NULL,
  PRIMARY KEY  (`ID`),
  KEY `comId` (`cost_comId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

简历表查询的存储过程：

CREATE PROCEDURE `search_resume`(comId INT,sex INT,place varchar(20),workStartDate INT,workEndDate INT,edu varchar(80),pro varchar(45),job VARCHAR(80))
BEGIN
	#组装条件
	DECLARE sb TEXT DEFAULT 'showstate=1 ';
	#要取的列
	DECLARE str TEXT DEFAULT 'SELECT id,post,myname,mysex,xueli,born_year,image,sendtime FROM perresume WHERE ';
	#专业循环需要用到的变量
	DECLARE proSize INT DEFAULT 0;
	#求职类别循环需要用到的变量
	DECLARE jobSize INT DEFAULT 0;
	#判断是否过滤性别
	IF sex > -1 AND sex < 2 THEN
		SET sb = CONCAT(sb,'and mysex=',sex);
	END IF;
	#过滤所在地区
	IF LENGTH(place)>0 THEN
		SET sb = CONCAT(sb,' and (SELECT FIND_IN_SET(place,cost_location) FROM com_search_table where cost_comId=',comId,')');
	END IF;
	#过滤学历
	IF LENGTH(edu)>0 THEN
		SET sb = CONCAT(sb,' and (SELECT FIND_IN_SET(xueli,cost_edu) FROM com_search_table where cost_comId=',comId,')');
	END IF;
	#过滤工作经验
	IF workStartDate >1 OR workEndDate<8 THEN
		SET sb  = CONCAT(sb,' and WorkYear>=',workStartDate,' and WorkYear<=',workEndDate);
	END IF;
	#过滤专业
	IF LENGTH(pro)>0 THEN
		SET sb = CONCAT(sb,' and (');
		WHILE INSTR(pro,',')>0 DO
			IF LEFT(pro,1)='A' THEN
				IF proSize=0 THEN
					SET sb = CONCAT(sb,' pro_a=',SUBSTRING(LEFT(pro,INSTR(pro,',')-1),2));
				ELSE
					SET sb = CONCAT(sb,' or pro_a=',SUBSTRING(LEFT(pro,INSTR(pro,',')-1),2));
				END IF;
			ELSE
				IF proSize=0 THEN
					SET sb = CONCAT(sb,' pro_b=',SUBSTRING(LEFT(pro,INSTR(pro,',')-1),2));
				ELSE
					SET sb = CONCAT(sb,' or pro_b=',SUBSTRING(LEFT(pro,INSTR(pro,',')-1),2));
				END IF;
			END IF;
			SET proSize = proSize+1;
			SET pro = SUBSTRING(pro,INSTR(pro,',')+1);
		END WHILE;
		SET sb = CONCAT(sb,')');
	END IF;
	#过滤求职类别
	IF LENGTH(job)>0 THEN
		SET sb = CONCAT(sb,' and (');
		WHILE INSTR(job,',')>0 DO
			IF LEFT(job,1)='A' THEN
				IF jobSize=0 THEN
					SET sb = CONCAT(sb,' JobMainClass=',SUBSTRING(LEFT(job,INSTR(job,',')-1),2));
				ELSE
					SET sb = CONCAT(sb,' or JobMainClass=',SUBSTRING(LEFT(job,INSTR(job,',')-1),2));
				END IF;
			ELSE
				IF jobSize=0 THEN
					SET sb = CONCAT(sb,' (JobMainClass=',LEFT(job,INSTR(job,'B')-1),' and JobSubClass=',SUBSTRING(LEFT(job,INSTR(job,',')-1),INSTR(job,'B')+1),')');
				ELSE
					SET sb = CONCAT(sb,' or (JobMainClass=',LEFT(job,INSTR(job,'B')-1),' and JobSubClass=',SUBSTRING(LEFT(job,INSTR(job,',')-1),INSTR(job,'B')+1),')');
				END IF;
			END IF;
			SET jobSize = jobSize+1;
			SET job = SUBSTRING(job,INSTR(job,',')+1);
		END WHILE;
		SET sb = CONCAT(sb,')');
	END IF;

	SET str = CONCAT(str,sb,' order by sendtime desc LIMIT 0,20');
	SELECT str;
	/*SET @sql:= str;
	PREPARE exec FROM @sql;
	EXECUTE exec;
	DEALLOCATE PREPARE exec;*/
END

请帮助我优化一下简历表的索引好吗？，企业可以通过设定任何条件查询简历库。

[wangkunhong]

比如调用：

call search_resume(172859,0,'1',1,6,'1','A12,A7,A11,A6,','1B0001,1B0004,1B0007,A3,A7,')

会产生：

EXPLAIN 
SELECT 
id,post,myname,mysex,xueli,born_year,image,sendtime 
FROM perresume 
WHERE 
	showstate=1 
	and mysex=0 
	and (SELECT FIND_IN_SET(place,cost_location) FROM com_search_table where cost_comId=172859) 
	and (SELECT FIND_IN_SET(xueli,cost_edu) FROM com_search_table where cost_comId=172859) 
	and ( pro_a=12 or pro_a=7 or pro_a=11 or pro_a=6) 
	and ( (JobMainClass=1 and JobSubClass=0001) or (JobMainClass=1 and JobSubClass=0004) or (JobMainClass=1 and JobSubClass=0007) or JobMainClass=3 or JobMainClass=7) 
order by sendtime desc LIMIT 0,20

这样的SQL，现在数据库有几十万数据，查询起来好慢，索引我也了解了些，最左原则我也明白，但是我头大，不知道怎么建才是最好的，所以请教各位老大了！

[懒得去死]

你的查询里面既有find_in_set，又有or 效率肯定高不到哪里去。想办法去掉这些，重新组合一下业务逻辑。

[wwwwb]

对字符操作一般速度慢，可以生成一个临时字段，将FIND_IN_SET(place,cost_location)结果替换此
临时字段，再操作。

[wangkunhong]

感谢楼上两位，你们的意思是把FIND_IN_SET也替换成or的格式对吗？
还有这些条件的索引如何建立才是最高效的呢？

[wwwwb]

不是，生成一个临时字段，将FIND_IN_SET(place,cost_location)结果替换此
临时字段，将
SELECT FIND_IN_SET(place,cost_location) FROM com_search_table where cost_comId=',comId,')'
中的FIND_IN_SET(place,cost_location)改为此
临时字段，操作试试
对字符操作，在在多数数据库中速度都比较慢，VFP、ACCESS中都是。

[wangkunhong]

thank's to wwwwb。

我尝试一下，但是在建立这些索引前，我还有些疑问，如mysex,showstate等字段的值均是1或者0，那么此类字段是不是不用建立索引？

如果不建立索引，那么这此条件是放在有索引字段的前面还是放在有索引条件的后面呢？

[wwwwb]

如mysex,showstate等字段的值均是1或者0，那么此类字段是不是不用建立索引？
要提高速度，就建立索引吧

如果不建立索引，那么这此条件是放在有索引字段的前面还是放在有索引条件的后面呢？
后面好一点吧

[wangkunhong]

thank's
谢谢您的指点，我再尝试一下看索引如何建立。