- 论坛徽章:
- 0
|
本帖最后由 cenalulu 于 2012-11-21 11:30 编辑
/////////////stu表保存学生分班名单///////////////////
CREATE TABLE IF NOT EXISTS `stu` (
`xq` TINYINT UNSIGNED NOT NULL , //学期
`sid` SMALLINT UNSIGNED NOT NULL , //学校id
`gid` TINYINT UNSIGNED NOT NULL , //年级id
`cid` SMALLINT UNSIGNED NOT NULL , //班级id
`sit` TINYINT UNSIGNED NOT NULL, //座位号
`num` BIGINT UNSIGNED NOT NULL , //学籍号
`flag` BOOL NOT NULL DEFAULT '1'
) ENGINE = MYISAM DEFAULT CHARSET=utf8;
ALTER TABLE `stu` ADD UNIQUE `id` (`xq`,`sid`,`num`);
ALTER TABLE `stu` ADD INDEX `idx` (`xq`,`sid`,`gid`,`cid`);
/////////////////acv表保存考试成绩///////////////////////
CREATE TABLE IF NOT EXISTS `acv`(
`ksid` SMALLINT UNSIGNED NOT NULL, //考试id
`kid` TINYINT UNSIGNED NOT NULL, //学科id
`num` BIGINT UNSIGNED NOT NULL, //学籍号
`val` FLOAT NULL DEFAULT NULL, //成绩值
`mtime` TIMESTAMP default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`flag` BOOL NOT NULL DEFAULT '1'
) ENGINE = MYISAM DEFAULT CHARSET=utf8;
ALTER TABLE `acv` ADD UNIQUE `id` (`ksid`, `kid`,`num`);
经常做的操作就是查询和统计,类似这样的查询字符串
$sqlstr="select `stu`.`sit`,`stu`.`gid`, `stu`.`cid`,`acv`.`kid`,`acv`.`val` from `stu` left join (select * from `acv` where `ksid`=${ksid_t})`acv` on `stu`.`num` = `acv`.`num` where `stu`.`xq` = ${xq} and `stu`.`sid` = ${sid} ";
if(!empty($gid)){
$sqlstr .= " and `stu`.`gid` = ${gid} ";
}
if(!empty($cid)){
$sqlstr .= " and `stu`.`cid` = ${cid} ";
}
请问各位大侠,我这样设计索引合理吗?acv表每年大约会有50万记录,stu表每年大约8万记录
|
|