Estoy tratando de optimizar esta consulta que ordenaposts porreputation campo (1er) y luegoid campo (2do). Sin la consulta del primer campo, toma ~ 0.250 segundos, pero con él toma hasta ~ 2.500 segundos (significa 10 veces más lento, terrible). ¿Cualquier sugerencia?

SELECT -- everything is ok here
FROM posts AS p
ORDER BY 
    -- 1st: sort by reputation if exists (1 reputation = 1 day)
    (CASE WHEN p.created_at >= unix_timestamp(now() - INTERVAL p.reputation DAY) 
        THEN +p.reputation ELSE NULL END) DESC, -- also used 0 instead of NULL
    -- 2nd: sort by id dec
    p.id DESC
WHERE p.status = 'published' -- the only thing for filter
LIMIT 0,10 -- limit provided as well

Notas:
- Uso de InnoDB (MySQL 5.7.19)
- Primaria esid enposts mesa
- Los campos están indexados tantocreated_at yreputation

Explicar el resultado:

# id,  select_type, table, partitions, type,  possible_keys, key,  key_len, ref,  rows,    filtered, Extra
# '1', 'SIMPLE',    'p',   NULL,       'ALL', NULL,          NULL, NULL,    NULL, '31968', '100.00', 'Using filesort'

ACTUALIZACIÓN ^^

La reputación establece que: Una publicación, cuántos días (n = reputación) se podrían mostrar en la parte superior de la lista.

En realidad, estaba tratando de dar reputación a algunas publicaciones que podrían aparecer en la parte superior de la lista, y encontrar esa solución:Ordene las publicaciones por "representante" pero solo por el límite de "un" día. Pero después de algún tiempo (aproximadamente 2 años), esa solución se convirtió en un problema ahora debido al aumento del volumen de datos de la tabla. Si no puedo resolver esto, entonces debería eliminar esa función del servicio.

ACTUALIZACIÓN ^^

-- all date's are unix timestamp (bigint)
SELECT p.*
    , u.name user_name, u.status user_status
    , c.name city_name, t.name town_name, d.name dist_name
    , pm.meta_name, pm.meta_email, pm.meta_phone
    -- gets last comment as json
    , (SELECT concat("{", 
        '"id":"', pc.id, '",', 
        '"content":"', replace(pc.content, '"', '\\"'), '",', 
        '"date":"', pc.date, '",', 
        '"user_id":"', pcu.id, '",', 
        '"user_name":"', pcu.name, '"}"') last_comment_json 
        FROM post_comments pc 
        LEFT JOIN users pcu ON (pcu.id = pc.user_id) 
        WHERE pc.post_id = p.id
        ORDER BY pc.id DESC LIMIT 1) AS last_comment
FROM posts p
    -- no issues with these
    LEFT JOIN users u ON (u.id = p.user_id)
    LEFT JOIN citys c ON (c.id = p.city_id)
    LEFT JOIN towns t ON (t.id = p.town_id)
    LEFT JOIN dists d ON (d.id = p.dist_id)
    LEFT JOIN post_metas pm ON (pm.post_id = p.id)
WHERE p.status = 'published'
GROUP BY p.id
ORDER BY 
    -- everything okay until here
    -- any other indexed fields makes query slow, not just "case" part
    (CASE WHEN p.created_at >= unix_timestamp(now() - INTERVAL p.reputation DAY) 
        THEN +p.reputation ELSE NULL END) DESC, 
    -- only id field (primary) is effective, no other indexes 
    p.id DESC
LIMIT 0,10;

Explique;

# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
1, PRIMARY, p, , ref, PRIMARY,user_id,status,reputation,created_at,city_id-town_id-dist_id,title-content, status, 1, const, 15283, 100.00, Using index condition; Using temporary; Using filesort
# dunno, these join's are not using, but if i remove returning fields from select part show "Using index condition"
1, PRIMARY, u, , eq_ref, PRIMARY, PRIMARY, 2, p.user_id, 1, 100.00, 
1, PRIMARY, c, , eq_ref, PRIMARY, PRIMARY, 1, p.city_id, 1, 100.00, 
1, PRIMARY, t, , eq_ref, PRIMARY, PRIMARY, 2, p.town_id, 1, 100.00, 
1, PRIMARY, d, , eq_ref, PRIMARY, PRIMARY, 2, p.dist_id, 1, 100.00, 
1, PRIMARY, pp, , eq_ref, PRIMARY, PRIMARY, 2, p.id, 1, 100.00, 
2, DEPENDENT SUBQUERY, pc, , ref, post_id,visibility,status, post_id, 2, func, 2, 67.11, Using index condition; Using where; Using filesort
2, DEPENDENT SUBQUERY, pcu, , eq_ref, PRIMARY, PRIMARY, 2, pc.user_id, 1, 100.00,