All geek questions in one place

JOIN very slowly when using the RIGHT JOINT on this request

I am having a problem with this request, which takes a few seconds to complete. I have already tried many optimizations, but at the moment I am removing the spaces.

The tables are as follows (and not fully normalized, in particular, the track 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 request that I am trying to execute at 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 delete the RIGHT JOINT and change it to JOIN, it quickly

EXPLAIN gives me this result, it seems that there is a little problem in choosing efave, but I don’t know how to do it

 +----+-------------+--------+--------+---------------+---------+---------+-----------------------+------+----------+--------------------------+ | 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 | | +----+-------------+--------+--------+---------------+---------+---------+-----------------------+------+----------+--------------------------+
+6
source share
2 answers

Your explanation looks clean, the only thing that stands out for me is that the esong table uses the utf8_bin association, and the track table does not have a specified sort, which means that it probably uses a different type of association . Try matching your sorts and see how the connection is made.

+5
source

Have you checked your execution plan? If not, run your request to enable it. Your right registration may perform index scans instead of index lookups. Or you may not have indexes. In any case, you need to look at your execution plan to better optimize your request. No one can really tell you how to do it faster using the Join (or join this question) right until you know what the real problem is. Here are some links .. For MySQL: http://dev.mysql.com/doc/refman/5.5/en/execution-plan-information.html For SqlServer: http://www.sql-server-performance.com/ 2006 / query-execution-plan-analysis /

0
source

Source: https://habr.com/ru/post/912447/

More articles:


All Articles