Question

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:

Description of the steps you can take to speed up the query.
The optimal indexes for this query, which you can copy and create in your database.
An automatically re-written query you can copy and execute in your database.

The optimization process and recommendations:

Avoid OR Conditions By Using UNION (modified query below): In mosts cases, filtering using the OR operator cannot be applied using indexes. A more optimized alternative will be to split the query to two parts combined with a UNION clause, while each query holds one part of the original OR condition.
Use Numeric Column Types For Numeric Values (query line: 42): Referencing a numeric value (e.g. 1) as a string in a WHERE clause might result in poor performance. Possible impacts of storing numbers as varchars: more space will be used, you won't be able to perform arithmetic operations, the data won't be self-validated, aggregation functions like SUM won't work, the output may sort incorrectly and more. If the column is numeric, remove the quotes from the constant value, to make sure a numeric comparison is done.
Use Numeric Column Types For Numeric Values (query line: 86): Referencing a numeric value (e.g. 1) as a string in a WHERE clause might result in poor performance. Possible impacts of storing numbers as varchars: more space will be used, you won't be able to perform arithmetic operations, the data won't be self-validated, aggregation functions like SUM won't work, the output may sort incorrectly and more. If the column is numeric, remove the quotes from the constant value, to make sure a numeric comparison is done.
Use Numeric Column Types For Numeric Values (query line: 128): Referencing a numeric value (e.g. 1) as a string in a WHERE clause might result in poor performance. Possible impacts of storing numbers as varchars: more space will be used, you won't be able to perform arithmetic operations, the data won't be self-validated, aggregation functions like SUM won't work, the output may sort incorrectly and more. If the column is numeric, remove the quotes from the constant value, to make sure a numeric comparison is done.
Use Numeric Column Types For Numeric Values (query line: 170): Referencing a numeric value (e.g. 1) as a string in a WHERE clause might result in poor performance. Possible impacts of storing numbers as varchars: more space will be used, you won't be able to perform arithmetic operations, the data won't be self-validated, aggregation functions like SUM won't work, the output may sort incorrectly and more. If the column is numeric, remove the quotes from the constant value, to make sure a numeric comparison is done.
Use UNION ALL instead of UNION (query line: 144): Always use UNION ALL unless you need to eliminate duplicate records. By using UNION ALL, you'll avoid the expensive distinct operation the database applies when using a UNION clause.

The optimized query:

SELECT
        username,
        uid,
        email,
        ui_gender,
        ui_country,
        ui_birthday,
        is_online,
        p_thumb_url,
        sr_id,
        st_love,
        age 
    FROM
        ((SELECT
            DISTINCT username AS username,
            user.id AS uid,
            email AS email,
            ui.gender AS ui_gender,
            ui.country AS ui_country,
            ui.birthday AS ui_birthday,
            IF(last_activity_date >= now() - INTERVAL 1 HOUR,
            1,
            0) AS is_online,
            p.thumb_url AS p_thumb_url,
            friend_request.id AS sr_id,
            IF(ul.id IS NOT NULL,
            1,
            0) AS st_love,
            DATE_FORMAT(NOW(),
            '%Y') - DATE_FORMAT(ui.birthday,
            '%Y') - (DATE_FORMAT(NOW(),
            '00-%m-%d') < DATE_FORMAT(ui.birthday,
            '00-%m-%d')) AS age 
        FROM
            friend_request 
        JOIN
            user 
                ON (
                    user.`id` = friend_request.`from_user_id`
                ) 
                AND user.`id` != '$user_id' 
                AND friend_request.`status` = '1' 
        JOIN
            user_info ui 
                ON user.`id` = ui.`user_id` 
        JOIN
            photo p 
                ON ui.`main_photo` = p.`id` 
        LEFT JOIN
            user_love ul 
                ON ul.`to_user_id` = user.`id` 
                AND ul.`from_user_id` = $user_id 
        WHERE
            (
                friend_request.`from_user_id` = '$user_id'
            ) 
        ORDER BY
            friend_request.id DESC LIMIT 30) 
    UNION
    DISTINCT (SELECT
        DISTINCT username AS username,
        user.id AS uid,
        email AS email,
        ui.gender AS ui_gender,
        ui.country AS ui_country,
        ui.birthday AS ui_birthday,
        IF(last_activity_date >= now() - INTERVAL 1 HOUR,
        1,
        0) AS is_online,
        p.thumb_url AS p_thumb_url,
        friend_request.id AS sr_id,
        IF(ul.id IS NOT NULL,
        1,
        0) AS st_love,
        DATE_FORMAT(NOW(),
        '%Y') - DATE_FORMAT(ui.birthday,
        '%Y') - (DATE_FORMAT(NOW(),
        '00-%m-%d') < DATE_FORMAT(ui.birthday,
        '00-%m-%d')) AS age 
    FROM
        friend_request 
    JOIN
        user 
            ON (user.`id` = friend_request.`from_user_id`) 
            AND user.`id` != '$user_id' 
            AND friend_request.`status` = '1' 
    JOIN
        user_info ui 
            ON user.`id` = ui.`user_id` 
    JOIN
        photo p 
            ON ui.`main_photo` = p.`id` 
    LEFT JOIN
        user_love ul 
            ON ul.`to_user_id` = user.`id` 
            AND ul.`from_user_id` = $user_id 
    WHERE
        (friend_request.`to_user_id` = '$user_id') 
    ORDER BY
        friend_request.id DESC LIMIT 30) 
UNION
DISTINCT (SELECT
    DISTINCT username AS username,
    user.id AS uid,
    email AS email,
    ui.gender AS ui_gender,
    ui.country AS ui_country,
    ui.birthday AS ui_birthday,
    IF(last_activity_date >= now() - INTERVAL 1 HOUR,
    1,
    0) AS is_online,
    p.thumb_url AS p_thumb_url,
    friend_request.id AS sr_id,
    IF(ul.id IS NOT NULL,
    1,
    0) AS st_love,
    DATE_FORMAT(NOW(),
    '%Y') - DATE_FORMAT(ui.birthday,
    '%Y') - (DATE_FORMAT(NOW(),
    '00-%m-%d') < DATE_FORMAT(ui.birthday,
    '00-%m-%d')) AS age 
FROM
    friend_request 
JOIN
    user 
        ON (user.`id` = friend_request.`to_user_id`) 
        AND user.`id` != '$user_id' 
        AND friend_request.`status` = '1' 
JOIN
    user_info ui 
        ON user.`id` = ui.`user_id` 
JOIN
    photo p 
        ON ui.`main_photo` = p.`id` 
LEFT JOIN
    user_love ul 
        ON ul.`to_user_id` = user.`id` 
        AND ul.`from_user_id` = $user_id 
WHERE
    (friend_request.`from_user_id` = '$user_id') 
ORDER BY
    friend_request.id DESC LIMIT 30) 
UNION
DISTINCT (SELECT
DISTINCT username AS username,
user.id AS uid,
email AS email,
ui.gender AS ui_gender,
ui.country AS ui_country,
ui.birthday AS ui_birthday,
IF(last_activity_date >= now() - INTERVAL 1 HOUR,
1,
0) AS is_online,
p.thumb_url AS p_thumb_url,
friend_request.id AS sr_id,
IF(ul.id IS NOT NULL,
1,
0) AS st_love,
DATE_FORMAT(NOW(),
'%Y') - DATE_FORMAT(ui.birthday,
'%Y') - (DATE_FORMAT(NOW(),
'00-%m-%d') < DATE_FORMAT(ui.birthday,
'00-%m-%d')) AS age 
FROM
friend_request 
JOIN
user 
    ON (user.`id` = friend_request.`to_user_id`) 
    AND user.`id` != '$user_id' 
    AND friend_request.`status` = '1' 
JOIN
user_info ui 
    ON user.`id` = ui.`user_id` 
JOIN
photo p 
    ON ui.`main_photo` = p.`id` 
LEFT JOIN
user_love ul 
    ON ul.`to_user_id` = user.`id` 
    AND ul.`from_user_id` = $user_id 
WHERE
(friend_request.`to_user_id` = '$user_id') 
ORDER BY
friend_request.id DESC LIMIT 30)
) AS union1 
ORDER BY
union1.sr_id DESC LIMIT 30

How to optimize this SQL query?

The optimization process and recommendations:

The optimized query:

Related Articles