求救mysql优化问题
本帖最后由 haoyg123 于 2008-12-24 13:07 编辑CREATE TABLE `eos_softs` (
`id` int(10) NOT NULL auto_increment,
`name` varchar(50) NOT NULL default '',
`accessurl` varchar(50) default NULL,
`downloaded` int(20) default '0',
`picture` varchar(50) default NULL,
`source` varchar(25) default NULL,
`content` tinytext,
`type_id` int(5) default '0',
`url` tinyint(2) default '0',
`status` tinyint(2) default '1',
`keywords` varchar(50) default NULL,
`clicked` int(9) default '1',
`created` int(11) default NULL,
`modified` int(11) default NULL,
PRIMARY KEY(`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=25 ;
以上是表结构，
用EXPLAIN SELECT *
FROM eos_softs
ORDER BY id DESC
LIMIT 0 , 10
为什么会出现filesort 表中没有足够的行让mysql的优化器来选择
mysql> CREATE TABLE `eos_softs` (
-> `id` int(10) NOT NULL auto_increment,
-> `name` varchar(50) NOT NULL default '',
-> `accessurl` varchar(50) default NULL,
-> `downloaded` int(20) default '0',
-> `picture` varchar(50) default NULL,
-> `source` varchar(25) default NULL,
-> `content` tinytext,
-> `type_id` int(5) default '0',
-> `url` tinyint(2) default '0',
-> `status` tinyint(2) default '1',
-> `keywords` varchar(50) default NULL,
-> `clicked` int(9) default '1',
-> `created` int(11) default NULL,
-> `modified` int(11) default NULL,
-> PRIMARY KEY(`id`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=25 ;
Query OK, 0 rows affected (0.04 sec)
mysql> EXPLAIN SELECT *
-> FROM eos_softs
-> ORDER BY id DESC
-> LIMIT 0 , 10 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: eos_softs
type: system
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 0
Extra: const row not found
1 row in set (0.05 sec)
mysql> insert into eos_softs values();
Query OK, 1 row affected (0.00 sec)
mysql> EXPLAIN SELECT * FROM eos_softs ORDER BY id DESC LIMIT 0 , 10\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: eos_softs
type: system
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1
Extra:
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT id FROM eos_softs ORDER BY id DESC LIMIT 0 , 10\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: eos_softs
type: system
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1
Extra:
1 row in set (0.00 sec)
mysql> insert into eos_softs values();
Query OK, 1 row affected (0.00 sec)
mysql> insert into eos_softs values();
Query OK, 1 row affected (0.00 sec)
mysql> insert into eos_softs values();
Query OK, 1 row affected (0.00 sec)
mysql> insert into eos_softs values();
Query OK, 1 row affected (0.00 sec)
mysql> insert into eos_softs values();
Query OK, 1 row affected (0.00 sec)
mysql> EXPLAIN SELECT id FROM eos_softs ORDER BY id DESC LIMIT 0 , 10\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: eos_softs
type: index
possible_keys: NULL
key: PRIMARY
key_len: 4
ref: NULL
rows: 6
Extra: Using index
1 row in set (0.00 sec)
页:
[1]