GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

LEFT JOIN performances

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • LEFT JOIN performances

    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 )

    Comment


    • #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.

      Comment


      • #4
        I actually meant "magics.id is null", as I wanted to list all the rows in samples not having any related row in magics (magics.sample_id=samples.id) )
        Perhaps there is better way to do this, I am far from being an SQL expert.
        So in case there is a better way to achieve this, I would be happy to learn )

        Comment

        Working...
        X