Databases
StackOverflow2013
Postsdbo
POJOIN very slow when using RIGHT JOIN on this query
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POJOIN very slow when using RIGHT JOIN on this query
    primarykey
    data
    text
    <p>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.</p> <p>The tables are the following (and are not absolutely normalized fully especially the tracks table)</p> <pre><code>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 </code></pre> <p>and the query I'm trying to execute with a normal speed is the following</p> <pre><code>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'); </code></pre> <p>Where if you remove the RIGHT JOIN and change it to JOIN it is fast</p> <p>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</p> <pre><code>+----+-------------+--------+--------+---------------+---------+---------+-----------------------+------+----------+--------------------------+ | 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 | | +----+-------------+--------+--------+---------------+---------+---------+-----------------------+------+----------+--------------------------+ </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USWessie
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POJOIN very slow when using RIGHT JOIN on this query
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POJOIN very slow when using RIGHT JOIN on this query
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POJOIN very slow when using RIGHT JOIN on this query
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWhat is the `COLLATE` value for the `tracks` table?
      singulars
      1. POJOIN very slow when using RIGHT JOIN on this query
      1. USMike Purcell
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload