8 Replies - 334 Views - Last Post: 19 July 2017 - 02:12 AM

#1 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 4136
  • View blog
  • Posts: 13,056
  • Joined: 08-June 10

resolving an FK reference takes too long

Posted 18 July 2017 - 08:12 AM

Hello,

I have an issue resolving an FK that I cant explain. The query I'm running lists the connections (i.e. cables) between switches and routers. That query runs fine as long as I don't try to resolve the FK on DevicePort.device (to get the device name). If I do, then it takes ages, although the relevant columns are all indexed. Can anyone explain why this happens or how that can be resolved?

SELECT 
    du.name 
FROM 
    InventorySwitch isw 
INNER JOIN 
    DevicePort dp 
    ON 
        isw.device = dp.device 
INNER JOIN 
    DeviceConnection dc 
    ON 
        dp.id IN(dc.source, dc.target) 
INNER JOIN 
    DevicePort tgt 
    ON 
        tgt.id IN(dc.source, dc.target) AND tgt.id <> dp.id 
-- this is the bottleneck:
INNER JOIN 
    DeviceUnit du 
    ON 
        tgt.device = du.id



Table setups
CREATE TABLE InventorySwitch (
    id int(11) NOT NULL AUTO_INCREMENT,
    device int(11) NOT NULL,
    -- the remainder fields are irrelevant to the query
    PRIMARY KEY (id),
    UNIQUE KEY device (device)
) ENGINE=InnoDB

CREATE TABLE DeviceUnit (
    id int(11) NOT NULL AUTO_INCREMENT,
    type varchar(31) COLLATE utf8_unicode_ci NOT NULL,
    name varchar(127) COLLATE utf8_unicode_ci NOT NULL,
    PRIMARY KEY (id),
    KEY type (type),
    CONSTRAINT DeviceUnit_ibfk_1 FOREIGN KEY (type) REFERENCES ItemType (name) ON UPDATE CASCADE
) ENGINE=InnoDB

CREATE TABLE DevicePort (
    id int(11) NOT NULL AUTO_INCREMENT,
    device int(11) NOT NULL,
    port varchar(31) COLLATE utf8_unicode_ci NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY device (device,port),
    KEY device_2 (device),
    CONSTRAINT DevicePort_ibfk_1 FOREIGN KEY (device) REFERENCES DeviceUnit (id) ON DELETE CASCADE
) ENGINE=InnoDB

CREATE TABLE DeviceConnection (
    id int(11) NOT NULL AUTO_INCREMENT,
    source int(11) NOT NULL,
    target int(11) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY source (source),
    UNIQUE KEY target (target),
    CONSTRAINT DeviceConnection_ibfk_1 FOREIGN KEY (source) REFERENCES DevicePort (id) ON DELETE CASCADE,
    CONSTRAINT DeviceConnection_ibfk_2 FOREIGN KEY (target) REFERENCES DevicePort (id) ON DELETE CASCADE
) ENGINE=InnoDB



Is This A Good Question/Topic? 0
  • +

Replies To: resolving an FK reference takes too long

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13489
  • View blog
  • Posts: 53,885
  • Joined: 12-June 08

Re: resolving an FK reference takes too long

Posted 18 July 2017 - 08:16 AM

What's the thought process of using the 'in' function in a join's "on"?
Was This Post Helpful? 0
  • +
  • -

#3 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 4136
  • View blog
  • Posts: 13,056
  • Joined: 08-June 10

Re: resolving an FK reference takes too long

Posted 18 July 2017 - 08:21 AM

If I wanted to query for a group of connections (e.g. all connections of a selected router), I don't know if its ports are in the source or target column.

This post has been edited by Dormilich: 18 July 2017 - 08:21 AM

Was This Post Helpful? 0
  • +
  • -

#4 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13489
  • View blog
  • Posts: 53,885
  • Joined: 12-June 08

Re: resolving an FK reference takes too long

Posted 18 July 2017 - 08:35 AM

What about - instead of using the 'IN' function you pop the 'device connection' table into a subquery where it is used in an union to get the key columns into the same column and goes for a regular join off that?

Example:

SELECT
    du.name
FROM
    InventorySwitch isw
JOIN DevicePort dp
    ON isw.device = dp.device
JOIN (
    SELECT id, source as MERGED_ID
    FROM DeviceConnection 

    UNION 

    SELECT id, target as MERGED_ID
    FROM DeviceConnection 
) dc 
    on dp.id = dc.merged_id
...

Was This Post Helpful? 0
  • +
  • -

#5 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 4136
  • View blog
  • Posts: 13,056
  • Joined: 08-June 10

Re: resolving an FK reference takes too long

Posted 18 July 2017 - 08:37 AM

Quote

What about - instead of using the 'IN' function you pop the 'device connection' table into a subquery [...]

That part of the query works fine. it's the last join in the query (to DeviceUnit) that is the troublemaker.
Was This Post Helpful? 0
  • +
  • -

#6 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13489
  • View blog
  • Posts: 53,885
  • Joined: 12-June 08

Re: resolving an FK reference takes too long

Posted 18 July 2017 - 08:38 AM

Sure, but this maybe a case more along the lines of a trickle down effect. Fix up the device connection and device port and see how that works with the last bit.
Was This Post Helpful? 1
  • +
  • -

#7 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 4136
  • View blog
  • Posts: 13,056
  • Joined: 08-June 10

Re: resolving an FK reference takes too long

Posted 18 July 2017 - 08:40 AM

I'll test it tomorrow. let's see how it turns out.
Was This Post Helpful? 0
  • +
  • -

#8 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 4136
  • View blog
  • Posts: 13,056
  • Joined: 08-June 10

Re: resolving an FK reference takes too long

Posted 19 July 2017 - 02:04 AM

I tried a query with just following the keys (i.e. catching only one of the two connection cases) and that run as fast as I would expect it. Beats me why that IN() clause destroys all the indexing, though ...

SELECT 
    du.name 
FROM 
    InventorySwitch isw 
INNER JOIN 
    DevicePort dp 
    ON 
        isw.device = dp.device 
INNER JOIN 
    DeviceConnection dc 
    ON 
        dp.id = dc.source 
INNER JOIN 
    DevicePort tgt 
    ON 
        tgt.id = dc.target 
INNER JOIN 
    DeviceUnit du 
    ON 
        tgt.device = du.id 


Was This Post Helpful? 0
  • +
  • -

#9 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 4136
  • View blog
  • Posts: 13,056
  • Joined: 08-June 10

Re: resolving an FK reference takes too long

Posted 19 July 2017 - 02:12 AM

Tried the UNION variant as well, not as fast as direct links, but still with good speed.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1