When I Order By A Computed Column, The Query Slows Significantly - Can This Be Sped Up?

The following query is taking more than 25 seconds to complete: SELECT c.* , (c.age+ (UNIX_TIMESTAMP()-UNIX_TIMESTAMP(c.newdate))) AS ranking , IF(uc.id_user = 7,1,0) AS favorite F

Solution 1:

(consolidating the discussion above)

You can't order efficiently by a computation result. To make this query work fast, create a ranking column that contains c.age-UNIX_TIMESTAMP(c.newdate). Then create an index on idandranking, i.e. CREATE INDEX id_ranking ON c (id, ranking) to make both GROUP BY and ORDER BY indexed.