LEFT JOIN performances

MySQL & MariaDB Other MySQL® Questions
1

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

2

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 )

3

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.