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

Question

表结构如下：
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=utfCREATE 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秒在右。
请问一下是什么原因呢，有办法优化吗？

Answer 1

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

Answer 2

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 ;

Answer 3

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

Answer 4

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

Answer 5

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`

Answer 6

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
====================================
主键名是哪个呢