# LEFT JOIN performances

**URL:** https://forums.percona.com/t/left-join-performances/1368
**Category:** Other MySQL® Questions
**Created:** [March 19, 2010, 5:44am UTC](https://forums.percona.com/t/left-join-performances/1368 "2010-03-19T05:44:46Z")
**Posts on this page:** 3
**Page:** 1

<div class="post-metadata">

### Author: ![xipe](https://avatars.discourse-cdn.com/v4/letter/x/a3d4f5/32.png) [@xipe](https://forums.percona.com/u/xipe)
#### Post date: [March 19, 2010, 5:44am UTC](https://forums.percona.com/t/left-join-performances/1368/1 "2010-03-19T05:44:46Z")

</div>

Hi all,

I am new on the forum, so I would like to thanks everyone in the community for being so helpful and patient )

I have got a performances issue with left join that seems strange to me.

Here is my query:

mysql\> select count(samples.id) from samples left join magics on magics.sample\_id=samples.id where magics.id is null;±------------------+| count(samples.id) |±------------------+| 0 | ±------------------+1 row in set (9 min 47.81 sec)

As you can see, it takes quite a while.

Here is the explain on the query:

mysql\> explain select count(samples.id) from samples left join magics on magics.sample\_id=samples.id where magics.id is null;±—±------------±--------±------±--------------±--------±--------±-----±------±------------------------+| id | select\_type | table | type | possible\_keys | key | key\_len | ref | rows | Extra |±—±------------±--------±------±--------------±--------±--------±-----±------±------------------------+| 1 | SIMPLE | samples | index | NULL | PRIMARY | 4 | NULL | 48822 | Using index | | 1 | SIMPLE | magics | ALL | NULL | NULL | NULL | NULL | 49496 | Using where; Not exists | ±—±------------±--------±------±--------------±--------±--------±-----±------±------------------------+

And the tables:

mysql\> show create table samples\G;\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* Table: samplesCreate Table: CREATE TABLE `samples` ( `id` int(11) NOT NULL auto\_increment, `path` varchar(255) collate utf8\_unicode\_ci NOT NULL, `created_at` datetime default NULL, `updated_at` datetime default NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO\_INCREMENT=49655 DEFAULT CHARSET=utf8 COLLATE=utf8\_unicode\_ci1 row in set (0.00 sec)ERROR: No query specifiedmysql\> show create table magics\G;\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* Table: magicsCreate Table: CREATE TABLE `magics` ( `id` int(11) NOT NULL auto\_increment, `value` varchar(255) collate utf8\_unicode\_ci NOT NULL, `sample_id` int(11) default NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO\_INCREMENT=48656 DEFAULT CHARSET=utf8 COLLATE=utf8\_unicode\_ci1 row in set (0.00 sec)ERROR: No query specified

Each table contains 48655 rows.

Is there any optimizations I can do for this query to be faster ?

## Thanks,

Raffaello

---

<div class="post-metadata">

### Author: ![xipe](https://avatars.discourse-cdn.com/v4/letter/x/a3d4f5/32.png) [@xipe](https://forums.percona.com/u/xipe)
#### Post date: [March 19, 2010, 7:31am UTC](https://forums.percona.com/t/left-join-performances/1368/2 "2010-03-19T07:31:13Z")

</div>

I added a missing index on magic.sample\_id and it goes quite fast. speed up from \> 9 minutes to 0.17 sec.

So problem solved, it was my bad )

---

<div class="post-metadata">

### Author: ![xaprb](https://avatars.discourse-cdn.com/v4/letter/x/49beb7/32.png) [@xaprb](https://forums.percona.com/u/xaprb)
#### Post date: [March 19, 2010, 7:52am UTC](https://forums.percona.com/t/left-join-performances/1368/3 "2010-03-19T07:52:51Z")

</div>

Glad you found it, I was about to reply ) Looks like you also might have meant to say

mysql\> select count(samples.id) from samples left join magics on magics.sample\_id=samples.id where magics.sample\_id is null;

notice the change in the WHERE clause.