|
以下是phpcms 产品和分类的数据库设计,表categeory 结构:
CREATE TABLE `category` (
`catid` smallint(5) unsigned NOT NULL auto_increment,
`siteid` smallint(5) unsigned NOT NULL default '0',
`module` varchar(15) NOT NULL,
`type` tinyint(1) unsigned NOT NULL default '0',
`modelid` smallint(5) unsigned NOT NULL default '0',
`parentid` smallint(5) unsigned NOT NULL default '0',
`arrparentid` varchar(255) NOT NULL,
`child` tinyint(1) unsigned NOT NULL default '0',
`arrchildid` mediumtext NOT NULL,
`catname` varchar(30) NOT NULL,
`style` varchar(5) NOT NULL,
`image` varchar(100) NOT NULL,
`description` mediumtext NOT NULL,
`parentdir` varchar(100) NOT NULL,
`catdir` varchar(30) NOT NULL,
`url` varchar(100) NOT NULL,
`items` mediumint(8) unsigned NOT NULL default '0',
`hits` int(10) unsigned NOT NULL default '0',
`setting` mediumtext NOT NULL,
`listorder` smallint(5) unsigned NOT NULL default '0',
`ismenu` tinyint(1) unsigned NOT NULL default '1',
`sethtml` tinyint(1) unsigned NOT NULL default '0',
`letter` varchar(30) NOT NULL,
`usable_type` varchar(255) NOT NULL,
`additional` text NOT NULL,
`commenttypeid` smallint(5) unsigned NOT NULL default '0',
PRIMARY KEY (`catid`),
KEY `module` (`module`,`parentid`,`listorder`,`catid`),
KEY `siteid` (`siteid`,`type`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=12101 ;
表中就catid 这个是大类的ID ,arrchildid这个是catid的子类所以arrchildid的值一般都是(1234,124,4578,..等),以下是产品表数据库设计:
CREATE TABLE `product` (
`id` mediumint(8) unsigned NOT NULL auto_increment,
`catid` int(10) unsigned NOT NULL default '0',
`typeid` smallint(5) unsigned NOT NULL,
`title` varchar(80) NOT NULL default '',
`style` char(24) NOT NULL default '',
`thumb` varchar(100) NOT NULL default '',
`keywords` varchar(40) NOT NULL default '',
`description` char(255) NOT NULL default '',
`posids` tinyint(1) unsigned NOT NULL default '0',
`url` char(100) NOT NULL,
`listorder` tinyint(3) unsigned NOT NULL default '30',
`status` tinyint(2) unsigned NOT NULL default '1',
`sysadd` tinyint(1) unsigned NOT NULL default '0',
`userid` mediumint(8) unsigned NOT NULL default '0',
`username` varchar(20) NOT NULL default '',
`inputtime` int(10) unsigned NOT NULL default '0',
`updatetime` int(10) unsigned NOT NULL default '0',
`areaid` int(10) unsigned NOT NULL default '0',
`price` float unsigned NOT NULL default '0',
`elite` varchar(255) NOT NULL default '0',
`material` varchar(255) NOT NULL default '',
`standards` varchar(255) NOT NULL default '',
`yongtu` varchar(255) NOT NULL default '',
`hits` int(5) NOT NULL,
PRIMARY KEY (`id`),
KEY `catid` (`catid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=971 ;
该表中catid 存的是分类的ID
该系统有筛选择功能 每一次程序先获得category 表中 catid值 再 获得该分类的arrchildid集合:所以筛选择商品分类sql语句如下:
select * from `product` where catid in (select `arrchildid` from `category` where catid=程序传进来的catid);
现在发现这样的语句超慢,求一个解决的方法 我试过用临时表存arrchildid集合然后用 inner join 做查询发现没什么提高,无奈求助各位高手 |
|