MySQL - Any addtional indexes would speed up this query?

Question

I see that my query does full table scan and takes a lot of time. I heard that making indexes would speed this up and I have added some to the tables. Is there any other indexes I should create to make this query faster?

My query is:

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

Here is the EXPLAIN result:

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

And here are the tables strucutre:

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

How to choose columns for building indexes, should I pick text columnts too or that only will work with numer columnts

Answer 1

email_routing needs

INDEX ( message_id, basket,  -- first, in either order
        receiver_email_id )  -- for "covering"

Your bk1 starts with receiver_email_id; this is not nearly as good.

1. Include column(s) in WHERE that are tested with =.
2. Include other columns from WHERE, GROUP BY, and ORDER BY (none in your case); the order is important, but beyond the scope of this discussion.
3. Include any other columns of the same table used anywhere in the query -- this is to make it a "covering" index. But don't bother if this would lead to more than, say, 5 columns or would involve TEXT, which cannot be in an index.

Then move on to the other tables. In both JOINs, it seems that they would be hit by their PRIMARY KEYs (JOIN x ON x.id = ...)

More discussion: Cookbook for creating indexes

On other issues...

You really should move to InnoDB. As of 5.6, it includes FULLTEXT, but there are some differences. In particular, you may need more fulltext indexes. For example, MATCH(lname, fname) requires FULLTEXT(lname, fname).

Do you really want to stick to cp1251? It limits your internalization mostly to English, Russian, Bulgarian, Serbian and Macedonian. And it is unclear how well FULLTEXT (MyISAM or InnoDB) will work with those non-English languages.

INTs are always 4 bytes; consider using smaller versions.

Is there really only one people? The Optimizer decided that was the best table to start with, but it wasn't. I'm hoping my improved index on email_routing will trick it into starting with that table -- which will definitely be optimal.

Answer 2

The table email_routing seem to have 1054618 rows .

And you try to find one row , by message_id .

e.message_id = 897360

BUT message_id must be indexed to speed-up the query .

message_id is part of the index bk1 , but this is not enough because message_id is not the first columns of the index .