mysql的right+join_mysql – 在此查询中使用RIGHT JOIN时JOIN非常慢

我遇到了这个查询的问题,需要几秒钟才能完成.我已经尝试了很多优化,但此时我正在拍摄空白.

表格如下(并没有完全标准化,特别是轨道表)

CREATE TABLE `tracks` (

`id` int(14) unsigned NOT NULL AUTO_INCREMENT,

`artist` varchar(200) NOT NULL,

`track` varchar(200) NOT NULL,

`album` varchar(200) NOT NULL,

`path` text NOT NULL,

`tags` text NOT NULL,

`priority` int(10) NOT NULL DEFAULT '0',

`lastplayed` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',

`lastrequested` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',

`usable` int(1) NOT NULL DEFAULT '0',

`accepter` varchar(200) NOT NULL DEFAULT '',

`lasteditor` varchar(200) NOT NULL DEFAULT '',

`hash` varchar(40) DEFAULT NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `hash` (`hash`),

FULLTEXT KEY `searchindex` (`tags`,`artist`,`track`,`album`),

FULLTEXT KEY `artist` (`artist`,`track`,`album`,`tags`)

) ENGINE=MyISAM AUTO_INCREMENT=3336 DEFAULT CHARSET=utf8

CREATE TABLE `esong` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`hash` varchar(40) COLLATE utf8_bin NOT NULL,

`len` int(10) unsigned NOT NULL,

`meta` text COLLATE utf8_bin NOT NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `hash` (`hash`)

) ENGINE=InnoDB AUTO_INCREMENT=16032 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

CREATE TABLE `efave` (

`id` int(10) unsigned NOT NULL DEFAULT '0',

`inick` int(10) unsigned NOT NULL,

`isong` int(10) unsigned NOT NULL,

UNIQUE KEY `inick` (`inick`,`isong`),

KEY `isong` (`isong`),

CONSTRAINT `inick` FOREIGN KEY (`inick`) REFERENCES `enick` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,

CONSTRAINT `isong` FOREIGN KEY (`isong`) REFERENCES `esong` (`id`) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `enick` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT

`nick` varchar(30) COLLATE utf8_bin NOT NULL,

`dta` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

`dtb` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',

PRIMARY KEY (`id`),

KEY `nick` (`nick`)

) ENGINE=InnoDB AUTO_INCREMENT=488 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

我试图以正常速度执行的查询如下

SELECT esong.meta, tracks.id FROM tracks RIGHT JOIN esong ON tracks.hash = esong.hash JOIN efave ON efave.isong = esong.id JOIN enick ON efave.inick = enick.id WHERE enick.nick = lower('nickname');

如果你删除RIGHT JOIN并将其更改为JOIN,那么它很快

EXPLAIN给了我这个结果,似乎efave选择中存在一个小问题,但我不知道如何解决这个问题

+----+-------------+--------+--------+---------------+---------+---------+-----------------------+------+----------+--------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+--------+--------+---------------+---------+---------+-----------------------+------+----------+--------------------------+

| 1 | SIMPLE | enick | ref | PRIMARY,nick | nick | 92 | const | 1 | 100.00 | Using where; Using index |

| 1 | SIMPLE | efave | ref | inick,isong | inick | 4 | radiosite.enick.id | 12 | 100.00 | Using index |

| 1 | SIMPLE | esong | eq_ref | PRIMARY | PRIMARY | 4 | radiosite.efave.isong | 1 | 100.00 | |

| 1 | SIMPLE | tracks | ALL | hash | NULL | NULL | NULL | 3210 | 100.00 | |

+----+-------------+--------+--------+---------------+---------+---------+-----------------------+------+----------+--------------------------+