CODEWITHSUNDEEP
mysqlsql
Ian Crew
Ian Crew

Reputation: 141

Optimizing MySQL query with nested select statements?

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...

Upvotes: 0

Views: 1249

Answers (2)

Pieter Geerkens
Pieter Geerkens

Reputation: 11883

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.

Upvotes: 0

Laurence
Laurence

Reputation: 10976

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

Upvotes: 1

Related Questions