视图查询排序慢，关联字段已加索引

[lshfong]

表结构如下：
CREATE TABLE `username` (
`id` int(10) NOT NULL auto_increment,
`username` varchar(50) character set utf8 collate utf8_unicode_ci default '',
`userpwd` varchar(50) character set utf8 collate utf8_unicode_ci default NULL,
`Role_id` varchar(50) character set utf8 collate utf8_unicode_ci default NULL,
`fid` smallint(6) default NULL,
`pathint` varchar(500) character set utf8 collate utf8_unicode_ci default NULL,
`locked` tinyint(1) default '1',
`name` varchar(100) character set utf8 collate utf8_unicode_ci default NULL,
`company` varchar(200) character set utf8 collate utf8_unicode_ci default NULL,
`credit` int(11) default '0',
`tel` varchar(100) character set utf8 collate utf8_unicode_ci default NULL,
`dizhi` varchar(100) character set utf8 collate utf8_unicode_ci default NULL,
`mail` varchar(80) character set utf8 collate utf8_unicode_ci default NULL,
`content` text character set utf8 collate utf8_unicode_ci,
`meun` varchar(200) default NULL,
`smsjk` tinyint(4) default '0',
`audit` tinyint(4) default '0',
`priority` tinyint(4) default '0',
`shi` timestamp NULL default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `user_name` (`user_name`)
) ENGINE=MyISAM AUTO_INCREMENT=49 DEFAULT CHARSET=utf

CREATE TABLE `phone` (
`id` bigint(20) NOT NULL auto_increment,
`sid` bigint(20) default NULL,
`needCallNumber` char(50) collate utf8_unicode_ci default '',
`caller` char(255) collate utf8_unicode_ci default NULL,
`content` varchar(300) collate utf8_unicode_ci default NULL,
`jk` varchar(50) collate utf8_unicode_ci default NULL,
`pathint` varchar(300) collate utf8_unicode_ci default NULL,
`shi` timestamp NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `NewIndex1` (`needCallNumber`)
) ENGINE=MyISAM AUTO_INCREMENT=93494 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
================================================================================================
视图userView中是用上表中的phone.needCallNumber和username.username,如果直接查询不排序的话，速度在0.078，如果查询排序select * from userView order by id desc limit 0,50,会慢很多。要1.8秒在右。
请问一下是什么原因呢，有办法优化吗？

[lshfong]

show index from `phone`
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
phone 0 PRIMARY 1 id A 93697 \N \N BTREE
phone 1 NewIndex1 1 needCallNumber A 9 \N \N YES BTREE
====================================
主键名是哪个呢

[wwwwb]

select * from userView a
inner join (select id from userView order by id desc limit 0,50) b
on a.id=b.id

[lshfong]

userView已经是一个视图

[wwwwb]

userView：代码是什么

[lshfong]

[Quote=引用 1 楼 wwwwb 的回复:]
select * from userView a
inner join (select id from userView order by id desc limit 0,50) b
on a.id=b.id
[/Quote]
这要查询速度能提高到1秒左右，还能更好一点吗

[lshfong]

[Quote=引用 3 楼 wwwwb 的回复:]
userView：代码是什么
[/Quote]
DELIMITER $$

DROP VIEW IF EXISTS `sms`.`userView`$$

CREATE ALGORITHM=UNDEFINED DEFINER=`admin`@`%` SQL SECURITY DEFINER VIEW `smslist` AS (select `phone`.`id` AS `id`,`username`.`fid` AS `fid`,`username`.`pathint` AS `pathint`,`phone`.`needCallNumber` AS `needCallNumber`,`phone`.`caller` AS `caller`,`phone`.`jk` AS `jk`,`phone`.`content` AS `content`,`phone`.`shi` AS `shi` from (`username` join `phone` on((`username`.`username` = `phone`.`needCallNumber`))))$$

DELIMITER ;

[ACMAIN_CHM]

如果你的查询没有任何WHERE条件子句，则只能是全表扫描。 但你可以强制使用phone的ID索引。

[lshfong]

强制使用phone的ID索引应该怎么写

[ACMAIN_CHM]

show index from `phone` ，看一下你的主键名是什么。


select `phone`.`id` AS `id`,`username`.`fid` AS `fid`,`username`.`pathint` AS `pathint`,
`phone`.`needCallNumber` AS `needCallNumber`,`phone`.`caller` AS `caller`,`phone`.`jk` AS `jk`,
`phone`.`content` AS `content`,`phone`.`shi` AS `shi`
from `username` join `phone` on`username`.`username` = `phone`.`needCallNumber`
force index 主键名
order by `phone`.`id`