0

I have two tables with relationship one to many.

create table locations
(
  id bigint 
     auto_increment 
     primary key,
  location_id varchar(128) 
     null,
  constraint locations_location_id_uindex 
     unique (location_id)
);


create table links
(
  id bigint 
     auto_increment 
     primary key,
  location_id varchar(128) 
     null,
  loc_id bigint 
     null,
  constraint links_location_id_fk 
     foreign key (loc_id) 
     references locations (id)
);

create index links_location_id_index
   on links (location_id);

The locations table has 596_752 records and links table has 1_221_450 ones. Both tables are very often updated with a new data, there is no a deletion process. The location_id in both tables has size 30 characters. When a new data appear in links table I have to find all links.location_id that do not exist in locations.location_id. In order to do so I execute the following query:

SELECT links.location_id 
FROM links
LEFT JOIN locations l 
   ON links.location_id = l.location_id
WHERE l.location_id IS NULL

The log output says:

34 rows retrieved starting from 1 in 1 m 11 s 678 ms (execution: 1 m 11 s 668 ms, fetching: 10 ms)

As you can see, it works too long and I am looking for a way how to improve it, can You help me?

EDIT The info about MySQL server, it's db.t2.micro AWS instance:

innodb_version  8.0.13
protocol_version    10
slave_type_conversions  ""
tls_version TLSv1,TLSv1.1,TLSv1.2
version 8.0.13
version_comment Source distribution
version_compile_machine x86_64
version_compile_os  Linux
version_compile_zlib    1.2.11

EXPLAIN results

2
  • Please consider reading this advice
    – mustaccio
    Commented Apr 22, 2019 at 20:28
  • Your scheme is potentially contradictory. Nothing prevents to have the FK link between records by some locations.id=links.loc_id whereas the links.location_id in the same record is equal to the value of locations.location_id from another, not referenced, record. The location_id in both tables has size 30 characters. So why you use varchar(128) and not char(30)?
    – Akina
    Commented Apr 23, 2019 at 4:47

2 Answers 2

0

Would you try this and let me know if this improves your performance:

SELECT links.location_id FROM links 
WHERE NOT Exists( 
select location_id from locations);
5
  • Hi, I've just tried your query select distinct links. location_id from links where links. location_id not in (select distinct location_id from locations), it executes 3s but then I tried the query from the question and it executes 2s. What could be the reason?
    – boden
    Commented Apr 23, 2019 at 6:06
  • Reasons for? For improved performance of your query? would you clarify a bit more? Commented Apr 23, 2019 at 6:14
  • Yes, what could be the reason for improved performance?
    – boden
    Commented Apr 23, 2019 at 7:25
  • Buffering of data into innodb buffer. Second executions onward you will see a much better performance. Commented Apr 23, 2019 at 9:09
  • Provide the EXPLAIN for both queries. You may find that they are identical.
    – Rick James
    Commented Apr 28, 2019 at 15:12
0

VARCHAR is not the villain. Having to scan 1.2M rows and do 1.2M lookups is the killer.

Why do you need to do this repeatedly? You say you check the entire table every time you add one row? Can't you just check the new row that you are adding? Please elaborate on this -- it is the key to shrinking the time from a minute to a few milliseconds.

And what do you do with the resulting 34 rows?

Why have a PRIMARY KEY of a id BIGINT? You may as well have location_id be the PRIMARY KEY in locations. And, since a PRIMARY KEY is a UNIQUE key, you can then get rid of the unique clause.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.