[Solved] alternative to nested select mysql

How to optimize this SQL query?

In case you have your own slow SQL query, you can optimize it automatically here.

For the query above, the following recommendations will be helpful as part of the SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid Selecting Unnecessary Columns (query line: 50): Avoid selecting all columns with the '*' wildcard, unless you intend to use them all. Selecting redundant columns may result in unnecessary performance degradation.
  2. Prefer IN Clause Over OR Conditions (modified query below): Using an IN clause is far more efficient than OR conditions, when comparing a column to more than one optional values. When using an IN clause, the database sorts the list of values and uses a quick binary search.
The optimized query:
SELECT
        post_id,
        post_order,
        post_parent,
        post_recycle,
        post_status,
        post_ps_id,
        post_v_id,
        post_src,
        post_sn_id,
        post_qpc_id,
        post_date_posted,
        post_scheduled_local_datetime,
        post_recycle_repeats,
        post_recycle_expiry_date,
        post_text,
        post_v_title,
        link_url,
        link_preview_removed,
        link_name,
        link_description,
        link_caption,
        link_url_is_bitlink,
        link_bitly_destination_url,
        link_expanded_url,
        link_initial_is_bitlink,
        link_destination,
        link_picture,
        link_picture_size,
        link_facebook_image,
        link_facebook_title,
        link_facebook_description,
        link_facebook_caption,
        link_twitter_card,
        link_twitter_image,
        link_twitter_title,
        link_twitter_description,
        pause_m_id,
        qpca_evergreen_too_frequent,
        sn_network,
        qpc_name,
        qpc_colour,
        ps_m_id,
        ps_filename,
        ps_via,
        ps_s3,
        ps_width,
        ps_height,
        ps_gif,
        video.*,
        (SELECT
            COUNT(*) 
        FROM
            post post_inner 
        WHERE
            (
                post_inner.post_parent = post.post_parent
            ) 
            AND post_inner.post_status = 'published') AS total_repeats 
    FROM
        post 
    JOIN
        social_network 
            ON sn_id = post_sn_id 
            AND sn_status = 'active' 
    JOIN
        queue_post_cat 
            ON qpc_id = post_qpc_id 
    LEFT JOIN
        queue_post_cat_account 
            ON qpca_qpc_id = post_qpc_id 
            AND qpca_sn_id = post_sn_id 
    LEFT JOIN
        link 
            ON link_id = post_link_id 
    LEFT JOIN
        pause 
            ON pause_m_id = qpc_m_id 
            AND pause_qpc_id = post_qpc_id 
            AND pause_sn_id = post_sn_id 
    LEFT JOIN
        photo_status 
            ON ps_id = post_ps_id 
    LEFT JOIN
        video 
            ON post_v_id = v_id 
            AND v_transcoded = 1 
    LEFT JOIN
        facebook 
            ON fb_db_id = sn_account_id 
            AND sn_network = 'facebook' 
    WHERE
        post_status != 'now' 
        AND post_m_id = 1 
        AND qpca_sn_id IS NOT NULL 
        AND qpca_qpc_id IS NOT NULL 
        AND post_status = 'queue' 
        AND (
            sn_network IN (
                'facebook', 'instagram', 'twitter'
            )
        ) 
        AND qpc_m_id = 1 
        AND (
            fb_type IS NULL 
            OR fb_type != 'profile'
        ) 
    ORDER BY
        post_order ASC

Related Articles



* original question posted on StackOverflow here.