why mysql nested select faster than join in my case?

0
votes

Hi Guys,

My problem below, i think the problem is my INNER JOIN snippet, how to optimize?

Database relationship:
enter image description here

Query Condition: sitename
Query Target: graph_path

SQL:

  1. Inner Join:

    SELECT graph.graph_path
    FROM sites
    INNER JOIN inventory on (sites.id = inventory.site_id)
    INNER JOIN host on (host.inventory_id = inventory.id)
    INNER JOIN graph on (graph.host_id = host.id)
    WHERE sites.sitename = '10071';

    Results:
    1 rows in set (2.74 sec)

    Explain:
    enter image description here

  2. Nested:

    SELECT graph_path
    FROM graph
    WHERE host_id = (select id from host where inventory_id = (select id from inventory where site_id = (select id from sites where sitename = '10071')));

    Results:
    1 row in set (0.03 sec)

    Explain:
    enter image description here

1
mysqljoin
show us the explain output of both - Drew
ok, i will show it later, thanks. - Henry
Did you run the two queries one after another without clearing query cache? - N.B.
It looks as if host doesn't have an index on inventory_id and/or host.inventory_id is not the same data type as inventory.id. Is either of those the case? If so, the this is confusing the optimizer into choosing a bad plan on the first query, and the second query is respectable but still not as fast as it could be. - Michael - sqlbot

1 Answers

0
votes

The optimal indexes for this query are: sites(sitename, id), inventory(site_id, id), host(inventory, id), graph(hostid, graph_path).

I am guessing that you only have indexes on the ids in the tables. In the first version, it does all the joins first and then filters by site. In the second, it probably scans sites first, and then progresses through the rest of the query quickly.