Not the answer you need?
Register and ask your own question!
Many Forum changes were implemented on Tue 22 Sep. Read about new Ranks, Scoring, and Reactions.
Email [email protected] for any comments or concerns.

LEFT JOIN performances

xipexipe EntrantCurrent User Role Beginner
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)</pre>


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 | +----+
+
+
+
+
+
+
+
+
+</pre>


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</pre>


Each table contains 48655 rows.

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

Thanks,
--
Raffaello

Comments

  • xipexipe Entrant Current User Role Beginner
    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 )
  • xaprbxaprb Mentor Inactive User Role Beginner
    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.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.