Join query optimization (cladr)?

Question

MySQL

There is a request

SELECT t1.id AS t1_id, t1.name AS t1_name, t1.base AS t1_base, t1.socr AS t1_socr, t1.code AS t1_code, t2.id AS t2_id, t2.name AS t2_name, t2.base AS t2_base, t2.socr AS t2_socr, t2.code AS t2_code, t3.id AS t3_id, t3.name AS t3_name, t3.socr AS t3_socr, t3.code AS t3_code, t3.zipcode AS t3_zipcode, t3.gninmb AS t3_gninmb, t3.uno AS t3_uno, t3.ocatd AS t3_ocatd
FROM kladr t1
JOIN kladr t2 ON LEFT( t1.code, 8 ) = LEFT( t2.code, 8 ) 
AND t2.name =  'Карлинское'
AND t2.socr =  'с'
JOIN street t3 ON LEFT( t2.code, 11 ) = LEFT( t3.code, 11 ) 
AND t3.name =  'Советская'
AND t3.socr =  'ул'
WHERE t1.code LIKE  '730%'
AND t1.name =  'Ульяновск'
AND t1.socr =  'г'
LIMIT 1

explain

Structure

CREATE TABLE IF NOT EXISTS `street` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `socr` varchar(128) NOT NULL,
  `code` varchar(128) NOT NULL,
  `zipcode` varchar(128) NOT NULL,
  `gninmb` varchar(128) NOT NULL,
  `uno` varchar(128) NOT NULL,
  `ocatd` varchar(128) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1032196 ;

CREATE TABLE IF NOT EXISTS `kladr` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `socr` varchar(255) DEFAULT NULL,
  `base` varchar(1000) NOT NULL,
  `code` varchar(255) DEFAULT NULL,
  `region` varchar(3) NOT NULL,
  `raion` varchar(3) NOT NULL,
  `city` varchar(3) NOT NULL,
  `town` varchar(3) NOT NULL,
  `zipcode` varchar(255) DEFAULT NULL,
  `gninmb` varchar(255) DEFAULT NULL,
  `uno` varchar(255) DEFAULT NULL,
  `ocatd` bigint(11) DEFAULT NULL,
  `status` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=215694 ;

Help optimize the query, I understand that in principle the problem is related to type All, but I don’t know how to connect these joins so that they work faster .. now it’s about 3-4 seconds query. with 2 GB RAM
UPD made code KEY
explain

Answer 1

Hang up an index on socr & name & code of the kladr table, socr & name of the street table.
Then they are the same, but in reverse order: name & socr & code
Compare which of the indexes will be more efficient.

Answer 2

Don't see the answer from explain ?
Everything is painted there, no keys.
The field on which the request is based (selection, I don’t know how to do it right) is better to add to the index, in your case it is`code`