Query slows down with ORDER BY

Question

I have a problem, I have a query and when I use the ORDER BY she gets too slow.

Note: I created an Index for the premium field and another for the score

Follow the full query:

SELECT 
    p.id AS id_freelancer,
    p.nome AS nome_freelancer,
    p.score,
    p.titulo_profissional,
    p.dh_premium_expires,
    p.premium,
    p.sobre_mim,
    p.tem_foto,
    p.nickname,
    p.qtde_projetos_concluidos AS qtde_projetos_concluidos,
    p.qtde_recomendacoes AS qtde_recomendacoes, 
    cp_habilidade.id AS id_habilidade,
    cp_habilidade.nome AS nome_habilidade,
    cp_area_de_interesse.id AS id_area_de_interesse,
    cp_sub_categoria.nome AS nome_sub_categoria
FROM
    (
        SELECT 
            DISTINCT cp_pessoa.id,
            cp_pessoa.nome,
            cp_pessoa.score,
            cp_freelancer.premium,
            cp_perfil.sobre_mim,
            cp_perfil.tem_foto,
            cp_freelancer.titulo_profissional,
            cp_freelancer.dh_premium_expires,
            cp_usuario.nickname,
            (
                SELECT count(cp_projeto.id) FROM cp_projeto 
                    WHERE cp_projeto.id_freelancer = cp_pessoa.id AND cp_projeto.id_status_projeto = 4
            ) AS qtde_projetos_concluidos,

            (
                SELECT count(cp_feedback_trabalho.id) FROM cp_feedback_trabalho
                    WHERE cp_feedback_trabalho.id_pessoa_que_recebeu = cp_pessoa.id AND cp_feedback_trabalho.recomenda = 1
            ) AS qtde_recomendacoes
        FROM cp_pessoa
            JOIN cp_perfil ON (cp_perfil.id_pessoa = cp_pessoa.id)
            JOIN cp_freelancer ON (cp_freelancer.id = cp_pessoa.id)
            JOIN cp_usuario ON (cp_usuario.id_pessoa = cp_pessoa.id) 
            LEFT JOIN cp_habilidade_freelancer ON (cp_habilidade_freelancer.id_freelancer = cp_pessoa.id)
            LEFT JOIN cp_habilidade ON (cp_habilidade.id = cp_habilidade_freelancer.id_habilidade)
            LEFT JOIN cp_area_de_interesse ON (cp_area_de_interesse.id_freelancer = cp_pessoa.id)
            LEFT JOIN cp_sub_categoria ON (cp_sub_categoria.id = cp_area_de_interesse.id_sub_categoria)
            LEFT JOIN cp_categoria ON (cp_categoria.id = cp_sub_categoria.id_categoria)
        WHERE cp_pessoa.id_tipo_usuario = 2 
            AND CASE WHEN cp_pessoa.score < 100 THEN 1 
                     WHEN cp_pessoa.score < 500 THEN 2 
                     WHEN cp_pessoa.score >= 500 THEN 3 
                END IN (1,2,3)
            AND cp_perfil.tem_foto = 1
            AND (cp_categoria.id = 1
              OR cp_categoria.id = 2
              OR cp_categoria.id = 3
              OR cp_categoria.id = 4
              OR cp_categoria.id = 5)

        ORDER BY cp_freelancer.premium DESC, cp_pessoa.score DESC LIMIT 0, 10
    ) p
    LEFT JOIN cp_habilidade_freelancer ON (cp_habilidade_freelancer.id_freelancer = p.id)
    LEFT JOIN cp_habilidade ON (cp_habilidade.id = cp_habilidade_freelancer.id_habilidade)
    LEFT JOIN cp_area_de_interesse ON (cp_area_de_interesse.id_freelancer = p.id)
    LEFT JOIN cp_sub_categoria ON (cp_sub_categoria.id = cp_area_de_interesse.id_sub_categoria)

    ORDER BY p.premium DESC, p.score DESC

Answer 1

Create an index in order DESC for both columns at the same time and exactly in this order: f.premium DESC and p.score DESC

Another suggestion, because they are different tables is to concatenate the two in the query and make a Order BY that concatenation...