Mysql self Join query with Limit never terminates

Database Administrators Asked by Rainer Yuan on October 28, 2021

I’m running an experiment with the query

Select  Distinct table_1.id1, table_2.id1
    FROM  image as table_1, image as table_2
    WHERE  table_1.id2 = table_2.id2
    LIMIT  K;

When I run the query without limit, it terminates after 8 hours. However, the query does not terminate for Limit 80000.

I’m running this on cloudLab with 100GB Ram and Below is the image of my.cnf and query plan. I’m not sure what is the bottle neck for my query. How should I solve this problem

Query Plan text:

| -> Limit: 80000 row(s)
  -> Table scan on <temporary>
        -> Temporary table with deduplication
            -> Limit table size: 80000 unique row(s)
                -> Inner hash join (table_2.id2 = table_1.id2)  (cost=48913990425076.15 rows=48913988916980)
                    -> Table scan on table_2  (cost=0.01 rows=22116507)
                    -> Hash
                       -> Table scan on table_1  (cost=2224194.70 rows=22116507)

enter image description here

enter image description here

One Answer

Your comma between two table means that you make a cross join and then select only the fitting rows.

do a proper JOIN with ON Clause, like

Select Distinct t1.id1, t2.id1 
FROM image as t1 INNER JOIN image as t2 
ON t1.id2 = t2.id2 

Also have an index on id2 in the table image

Answered by nbk on October 28, 2021

Add your own answers!

Related Questions

Dbca hangs at 59%

0  Asked on December 6, 2020 by user133197


Performance issue in Postgresql 12

0  Asked on December 3, 2020 by raghu-mutyam


Linked Server throws metadata error

1  Asked on November 30, 2020 by cam


What is an ODBK file and how to restore that?

1  Asked on November 14, 2020 by hasnain


Calculating percentage over sub query SQL

1  Asked on October 29, 2020 by patrick-kusebauch


How to select the rows affected by an update

2  Asked on October 27, 2020 by enharmonic


Corruption in mariadb binlog

2  Asked on October 18, 2020 by ctutte


“SQL command not properly ended” with trigger

1  Asked on September 28, 2020 by exilevoid


Ask a Question

Get help from others!

© 2023 All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP