Имеется запрос
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
Структура
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 ;
Помогите оптимизировать запрос, я понимаю что в принципе проблема связана с type All но я не знаю как связать эти джоины чтобы они быстрее работали.. сейчас это около 3-4 сек запрос. при 2 GB RAM
UPD сделал code KEY
explain
Средний
