# baffled by LEFT JOIN versus INNER JOIN speed

**URL:** https://forums.percona.com/t/baffled-by-left-join-versus-inner-join-speed/731
**Category:** Other MySQL® Questions
**Created:** [April 24, 2008, 2:10pm UTC](https://forums.percona.com/t/baffled-by-left-join-versus-inner-join-speed/731 "2008-04-24T14:10:14Z")
**Posts on this page:** 2
**Page:** 1

<div class="post-metadata">

### Author: ![jawadde](https://avatars.discourse-cdn.com/v4/letter/j/b77776/32.png) [@jawadde](https://forums.percona.com/u/jawadde)
#### Post date: [April 24, 2008, 2:10pm UTC](https://forums.percona.com/t/baffled-by-left-join-versus-inner-join-speed/731/1 "2008-04-24T14:10:14Z")

</div>

I’ve got a two-table setup (well, actually I have 7 tables, with 5 more Authorfunction\* tables like the one below, but these 5 don’t make much difference in the problem):

CREATE TABLE `authorfunctionA` (  
`ID` mediumint(9) NOT NULL default ‘0’,  
`value` varchar(255) NOT NULL default ‘’,  
KEY `ID` (`ID`),  
KEY `value` (`value`),  
FULLTEXT KEY `val` (`value`)  
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

and

CREATE TABLE `stories_full` (  
`ID` mediumint(9) NOT NULL default ‘0’,  
`title` varchar(255) NOT NULL default ‘’,  
`displaytitle` varchar(255) NOT NULL default ‘’,  
`story` mediumtext NOT NULL,  
PRIMARY KEY (`ID`)  
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

the first table has 165.000 records, the 2nd table has 40.000 records. If I run the query below, mysql needs a full 2 seconds to compute :

SELECT stories\_full.\* FROM stories\_full LEFT JOIN authorfunctionA AS field\_0 ON field\_0.id=stories\_full.id WHERE (MATCH(field\_0.value) AGAINST (‘+Roc\*’ IN BOOLEAN MODE))

now if I run the same query with a normal join :

SELECT stories\_full.\* FROM stories\_full JOIN authorfunctionA AS field\_0 ON field\_0.id=stories\_full.id WHERE (MATCH(field\_0.value) AGAINST (‘+Roc\*’ IN BOOLEAN MODE)), the result is done in 0.002 seconds

(this query is still relatively simple : in real life, i’m running 4 joins, and the queries takes up to 15 seconds sometimes)

here are the explains for the 2 queries :

±—±------------±-------------±-----±--------------±-----±--------±-------------------------±-------±------------+| id | select\_type | table | type | possible\_keys | key | key\_len | ref | rows | Extra |±—±------------±-------------±-----±--------------±-----±--------±-------------------------±-------±------------+| 1 | SIMPLE | stories\_full | ALL | NULL | NULL | NULL | NULL | 164406 | | | 1 | SIMPLE | field\_0 | ref | ID | ID | 3 | ipbfull2.stories\_full.ID | 1 | Using where | ±—±------------±-------------±-----±--------------±-----±--------±-------------------------±-------±------------+

and

±—±------------±-------------±---------±--------------±--------±--------±--------------------±-----±------------+| id | select\_type | table | type | possible\_keys | key | key\_len | ref | rows | Extra |±—±------------±-------------±---------±--------------±--------±--------±--------------------±-----±------------+| 1 | SIMPLE | field\_0 | fulltext | ID,val | val | 0 | | 1 | Using where | | 1 | SIMPLE | stories\_full | eq\_ref | PRIMARY | PRIMARY | 3 | ipbfull2.field\_0.ID | 1 | | ±—±------------±-------------±---------±--------------±--------±--------±--------------------±-----±------------+

so apparently, the LEFT JOIN is simply not using the index !!!

I’ve tested this on mysql 4.1.14 and 5.0.45 with the same results

can anyone shed some light on this matter ? I’m totally in the dark on how to optimize this (

maaaaaany many thanks in advance !

ah, a PS : i realised that you might think the query execution order could be in play : running a LEFT join, followed by an inner join might have some query cached. So I ran a dozen more queries in mixed order (first the inner join, then the left join), and every time, the left join was substantially slower

---

<div class="post-metadata">

### Author: ![cybergroupdoug](https://avatars.discourse-cdn.com/v4/letter/c/58956e/32.png) [@cybergroupdoug](https://forums.percona.com/u/cybergroupdoug)
#### Post date: [April 28, 2008, 5:22am UTC](https://forums.percona.com/t/baffled-by-left-join-versus-inner-join-speed/731/2 "2008-04-28T05:22:13Z")

</div>

I think that the issue at play here is a misunderstanding of how a LEFT JOIN works.

An INNER JOIN selects only those records which are common to both tables. Thus you notice that the number of records operated on is only 1, and both indexes are used.

A LEFT JOIN selects all records from the left side, and whatever records from the right side that match up, filling in NULL values for the right side where there’s no match. That’s why it did a full scan of the stories\_full table, but used the index on the field\_0 table.

Unless there’s a real need for a LEFT JOIN, don’t use it. In this case, you really don’t need it, since you’re only returning values from the stories\_full table, and only using the other table to narrow down the results you’re going to return.

-Doug

EDIT: Corrected FULL to INNER JOIN.