Optimizing MySQL query with nested select statements?

Question

I've got read-only access to a MySQL database, and I need to loop through the following query about 9000 times, each time with a different $content_path_id. I'm calling this from within a PERL script that's pulling the '$content_path_id's from a file.

SELECT an.uuid FROM alf_node an WHERE an.id IN 
    (SELECT anp.node_id FROM alf_node_properties anp WHERE anp.long_value IN 
        (SELECT acd.id FROM alf_content_data acd WHERE acd.content_url_id = $content_path_id));

Written this way, it's taking forever to do each query (approximately 1 minute each). I'd really rather not wait 9000+ minutes for this to complete if I don't have to. Is there some way to speed up this query? Maybe via a join? My current SQL skills are embarrassingly rusty...

Why not have all the $content_path_id in a single table, join that table with alf_content_data (on content_url_id), then join the result with alf_node_properties, then join the result with alf_node? — Nitzan Shaked
– Nitzan Shaked, Commented Sep 1, 2013 at 20:45
EXISTS could be faster than IN. See stackoverflow.com/questions/14190788/… — PM 77-1
– PM 77-1, Commented Sep 1, 2013 at 20:47
Thanks Nitzan. Unfortunately, I only have read-only access to the DB, so I can't add a table. — Ian Crew
– Ian Crew, Commented Sep 1, 2013 at 21:01

Laurence · Accepted Answer · 2013-09-01 20:50:08Z

1

This is an equivalent query using joins. It depends what indexes are defined on the tables how this will perform.

If your Perl interface has the notion of prepared statements, you may be able to save some time by preparing once and executing with 9000 different binds.

You could also possibly save time by building one query with a big acd.content_url_id In ($content_path_id1, $content_path_id2, ...) clause

Select
    an.uuid 
From
    alf_node an
        Inner Join
    alf_node_properties anp
        On an.id = anp.node_id 
        Inner Join
    alf_content_data acd 
        On anp.long_value = acd.id 
Where
    acd.content_url_id = $content_path_id

edited Sep 1, 2013 at 20:50

answered Sep 1, 2013 at 20:45

Laurence

11k1 gold badge28 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ian Crew Over a year ago

Thanks Laurence: That seems somewhat, but not a whole lot, faster. I wonder if I could make it faster by putting all 9000 queries as "OR"s in the "where" line and executing it as one jumbo query. Would that help?

Laurence Over a year ago

If there's no index on alf_content_data (content_url_id, acd.id) then it will almost certainly make it faster. My suggestion with `acd.content_url_id in ($content_path_id1, ..) etc is equivalent to this.

Ian Crew Over a year ago

Sorry--I didn't notice the "acd.content_url_id In" bit in your original answer. That did indeed help on a smaller test, so the full-size one is running now. Thanks!

Ian Crew Over a year ago

The whole, longer, query just completed in under 2 minutes, so I can confirm that it the "acd.content_url_id IN" bit does in fact greatly speed things up. Thanks again Laurence!

Pieter Geerkens · Accepted Answer · 2013-09-01 21:51:04Z

0

Try this extension to Laurence's solution which replaces the long list of OR's with an additional JOIN:

Select
    an.uuid 
From alf_node an
Join alf_node_properties anp
    On an.id = anp.node_id 
Join alf_content_data acd 
    On anp.long_value = acd.id 
Join (
    select "id1" as content_path_id union all
    select "id2" as content_path_id union all
    /* you get the idea */
    select "idN" as content_path_id 
) criteria   
    On acd.content_url_id = criteria.content_path_id

I have used SQL Server syntax above but you should be able to translate it readily.

answered Sep 1, 2013 at 21:51

Pieter Geerkens

11.9k2 gold badges35 silver badges54 bronze badges

Collectives™ on Stack Overflow

Optimizing MySQL query with nested select statements?

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related