I'm having a problem with this query that takes several seconds to complete. I already tried many optimizations but I'm shooting blanks at this point.
The tables are the following (and are not absolutely normalized fully especially the tracks table)
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
and the query I'm trying to execute with a normal speed is the following
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');
Where if you remove the RIGHT JOIN and change it to JOIN it is fast
The EXPLAIN gives me this result, it seems there is a small problem in the efave selection but I have no idea how to get that out
+----+-------------+--------+--------+---------------+---------+---------+-----------------------+------+----------+--------------------------+
| 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 | |
+----+-------------+--------+--------+---------------+---------+---------+-----------------------+------+----------+--------------------------+
Your explain looks clean, the only thing that stands out to me is the fact that the esong table is using a collate of utf8_bin, and the tracks table doesn't have a collation specified, which means it is probably using another collation type. Try aligning your collations and see how the join performs.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With