我看到我的查询执行全表扫描,需要花费很多时间。我听说做索引会加快速度,我已经在表中添加了一些索引。我是否应该创建其他索引来加快查询速度?
我的问题是:
SELECT p.id, n.people_type_id, n.full_name, n.post, p.nick,
p.key_name, p.email, p.internal_user_id FROM email_routing e
JOIN people_emails p ON p.id=e.receiver_email_id
JOIN people n ON n.id = p.people_id
WHERE e.message_id = 897360 AND e.basket=1
以下是解释结果:
EXPLAIN SELECT p.id, n.people_type_id, n.full_name, n.post, p.nick,
p.key_name, p.email, p.internal_user_id FROM email_routing e
JOIN people_emails p ON p.id=e.receiver_email_id
JOIN people n ON n.id = p.people_id
WHERE e.message_id = 897360 AND e.basket=1
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE n NULL ALL PRIMARY NULL NULL NULL 1 100.00 NULL
1 SIMPLE p NULL ALL PRIMARY NULL NULL NULL 3178 10.00 Using where; Using join buffer (Block Nested Loop)
1 SIMPLE e NULL ref bk1 bk1 4 server.p.id 440 1.00 Using where; Using
这是桌子的结构:
SHOW CREATE TABLE people_emails;
CREATE TABLE `people_emails` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`nick` varchar(255) NOT NULL,
`email` varchar(255) NOT NULL,
`key_name` varchar(255) NOT NULL,
`people_id` int(11) NOT NULL,
`status` int(11) NOT NULL DEFAULT '0',
`activity` int(11) NOT NULL,
`internal_user_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
FULLTEXT KEY `email` (`email`)
) ENGINE=MyISAM AUTO_INCREMENT=22114 DEFAULT CHARSET=utf8
SHOW CREATE TABLE email_routing;
CREATE TABLE `email_routing` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`message_id` int(11) NOT NULL,
`sender_email_id` int(11) NOT NULL,
`receiver_email_id` int(11) NOT NULL,
`basket` int(11) NOT NULL,
`status` int(11) NOT NULL,
`popup` int(11) NOT NULL DEFAULT '0',
`tm` int(11) NOT NULL DEFAULT '0',
KEY `id` (`id`),
KEY `bk1` (`receiver_email_id`,`status`,`sender_email_id`,`message_id`,`basket`),
KEY `bk2` (`sender_email_id`,`tm`)
) ENGINE=InnoDB AUTO_INCREMENT=1054618 DEFAULT CHARSET=utf8
SHOW CREATE TABLE people;
CREATE TABLE `people` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`fname` varchar(255) CHARACTER SET cp1251 NOT NULL,
`lname` varchar(255) CHARACTER SET cp1251 NOT NULL,
`patronymic` varchar(255) CHARACTER SET cp1251 NOT NULL,
`gender` tinyint(1) NOT NULL,
`full_name` varchar(255) NOT NULL DEFAULT ' ',
`category` int(11) NOT NULL,
`people_type_id` int(255) DEFAULT NULL,
`tags` varchar(255) CHARACTER SET cp1251 NOT NULL,
`job` varchar(255) CHARACTER SET cp1251 NOT NULL,
`post` varchar(255) CHARACTER SET cp1251 NOT NULL,
`profession` varchar(255) CHARACTER SET cp1251 DEFAULT NULL,
`zip` varchar(16) CHARACTER SET cp1251 NOT NULL,
`country` int(11) DEFAULT NULL,
`region` varchar(10) NOT NULL,
`city` varchar(255) CHARACTER SET cp1251 NOT NULL,
`address` varchar(255) CHARACTER SET cp1251 NOT NULL,
`address_date` date DEFAULT NULL,
`inner` tinyint(4) NOT NULL,
`contact_through` varchar(255) DEFAULT '',
`next_call` date NOT NULL,
`additional` text CHARACTER SET cp1251 NOT NULL,
`user_id` int(11) NOT NULL,
`changed` datetime NOT NULL,
`status` int(11) DEFAULT NULL,
`nick` varchar(255) DEFAULT NULL,
`birthday` date DEFAULT NULL,
`last_update_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`area` text NOT NULL,
`reviewed_` tinyint(4) NOT NULL,
`phones_old` text NOT NULL,
`post_sticker` text NOT NULL,
`permissions` int(120) NOT NULL DEFAULT '0',
`internal_user_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `most_used` (`category`,`status`,`city`,`lname`,`next_call`),
KEY `registrars` (`category`,`status`,`contact_through`,`next_call`),
FULLTEXT KEY `lname` (`lname`),
FULLTEXT KEY `fname` (`fname`),
FULLTEXT KEY `mname` (`patronymic`),
FULLTEXT KEY `Full Name` (`full_name`)
) ENGINE=MyISAM AUTO_INCREMENT=415009 DEFAULT CHARSET=utf8
如何选择用于生成索引的列,是否也应选择文本列,或者只使用数字列
