运行环境: centos5.0 mysql5.0.45-log
php语言
my.cnf 配置如下:[mysqld]
default-character-set = gbk
port= 3306
socket= /tmp/mysql.sock
skip-locking
key_buffer = 32M
key_buffer_size = 256M
max_allowed_packet = 1M
table_cache = 256
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
log-slow-queries = /data/www/wwwroot/bbs/mysqld-slow-query.log
log = /data/www/wwwroot/bbs/mysqld-general-query.log
long-query-time = 5
log-long-format
log-queries-not-using-indexes
server-id= 1
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
default-character-set = gbk
no-auto-rehash
[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
query_cache_type = 1
query_cache_size = 200M
query_cache_limit = 1048576
表结构:
cdb_lu_digestimages_images CREATE TABLE `cdb_lu_digestimages_images` (
`id` int(10) unsigned NOT NULL auto_increment,
`aid` int(10) unsigned NOT NULL,
`title` char(100) NOT NULL,
`description` char(100) NOT NULL,
`tid` int(10) NOT NULL,
`uid` int(8) unsigned NOT NULL default '0',
`fid` smallint(6) unsigned NOT NULL default '0',
`pid` int(10) NOT NULL,
`typeid` smallint(5) unsigned NOT NULL,
`thumbnail` char(60) NOT NULL,
`dateline` int(10) unsigned NOT NULL default '0',
`adddateline` int(10) unsigned NOT NULL default '0',
`adduid` mediumint(8) unsigned NOT NULL default '0',
`isshow` tinyint(1) NOT NULL,
`iscall` tinyint(1) NOT NULL,
`width` smallint(5) unsigned NOT NULL default '180',
`height` smallint(5) unsigned NOT NULL default '180',
PRIMARY KEY (`id`),
UNIQUE KEY `aid` (`aid`),
KEY `title` (`title`),
KEY `description` (`description`),
KEY `tid` (`tid`),
KEY `typeid` (`typeid`),
KEY `isshow` (`isshow`),
KEY `iscall` (`iscall`),
KEY `isshow_orderby` (`tid`,`aid`)
) ENGINE=MyISAM AUTO_INCREMENT=475737 DEFAULT CHARSET=gbk
/*Index Information For - redocn.cdb_lu_digestimages_images*/
-------------------------------------------------------------
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
-------------------------- ---------- -------------- ------------ ----------- --------- ----------- -------- ------ ------ ---------- -------
cdb_lu_digestimages_images 0 PRIMARY 1 id A 398113 (NULL) (NULL) BTREE
cdb_lu_digestimages_images 0 aid 1 aid A 398113 (NULL) (NULL) BTREE
cdb_lu_digestimages_images 1 title 1 title A 39811 (NULL) (NULL) BTREE
cdb_lu_digestimages_images 1 description 1 description A 199056 (NULL) (NULL) BTREE
cdb_lu_digestimages_images 1 tid 1 tid A 39811 (NULL) (NULL) BTREE
cdb_lu_digestimages_images 1 typeid 1 typeid A 632 (NULL) (NULL) BTREE
cdb_lu_digestimages_images 1 isshow 1 isshow A 2 (NULL) (NULL) BTREE
cdb_lu_digestimages_images 1 iscall 1 iscall A 2 (NULL) (NULL) BTREE
cdb_lu_digestimages_images 1 isshow_orderby 1 tid A 39811 (NULL) (NULL) BTREE
cdb_lu_digestimages_images 1 isshow_orderby 2 aid A 398113 (NULL) (NULL) BTREE
表明:cdb_lu_digestimages_images 其中共有398113条记录
语句1:SELECT * FROM cdb_lu_digestimages_images WHERE 1 AND isshow='1' ORDER BY iscall DESC, tid DESC, aid ASC LIMIT 0, 36
语句2:SELECT * FROM cdb_lu_digestimages_images WHERE 1 AND isshow='1' AND typeid='403' AND (title LIKE '%中国%' OR description LIKE '%中国%') ORDER BY iscall DESC, tid DESC, aid ASC LIMIT 0, 36
我应该如何优化?
现在查询至少9秒?
为了减轻服务器压力翻页限制在100页以内,一旦放开,mysql压力立马增加。
我应该怎么样建索引,以及设置mysql参数
2009年7月03日 16:22