Is it possible to index a computed column used in ORDER BY?

I am having problems with my mysql server, because of not properly written queries. I did not use indexes, because I do not know how to add them on computed columns, in a query that has SELECT COUNT(*) AS b ... ORDER BY b.

Looks like it is impossible, so if it is indeed impossible, how could I re-manage my queries?

SELECT COUNT(downloaded.id) AS downloaded_count
    , downloaded.file_name
    ,uploaded.* 
FROM `downloaded` JOIN uploaded 
ON downloaded.file_name = uploaded.file_name 
WHERE downloaded.completed = 1
AND uploaded.active = 1 
AND uploaded.nsfw = 0 
AND downloaded.datetime > DATE_SUB(NOW(), INTERVAL 7 DAY) 
GROUP BY downloaded.file_name 
ORDER BY downloaded_count DESC LIMIT 30;

EXPLAIN

+----+-------------+------------+------+---------------+-----------+---------+--------------------------+------+----------------------------------------------+
| id | select_type | table      | type | possible_keys | key       | key_len | ref                      | rows | Extra                                        |
+----+-------------+------------+------+---------------+-----------+---------+--------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | uploaded   | ALL  | file_name_up  | NULL      | NULL    | NULL                     | 3139 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | downloaded | ref  | file_name     | file_name | 767     | piqik.uploaded.file_name |    8 | Using where                                  |
+----+-------------+------------+------+---------------+-----------+---------+--------------------------+------+----------------------------------------------+

UPDATED:

with ORDER BY;

Showing rows 0 - 29 ( 30 total, Query took 0.1639 sec)

without ORDER BY;

Showing rows 0 - 29 ( 30 total, Query took 0.0064 sec)

UPDATED #2:

table: uploaded (Total 720.5 KiB)

``
CREATE TABLE IF NOT EXISTS uploaded (
id int(11) NOT NULL AUTO_INCREMENT,
sid

There are several things you can do to improve the efficiency of the query.

-
First, index the tables. OK, it's not trivial to do this. And any indexing should be considered in relation with all the queries you are running in the database. Lets pretend that this is the only query or that it is the most crucial one.

-
We'll break this task into the 2 tables involved. The first table, uploaded, appears - besides the join - only in the select list (all its columns) and in the 2 WHERE conditions:

AND uploaded.active = 1 
AND uploaded.nsfw = 0

Two simple equality conditions, so the immediate thought is to make an index on these two columns or better include the joining column (this is a technicality, as the table is using the MyISAM engine): (active, nsfw, file_name)

ALTER TABLE uploaded
  ADD INDEX active_nsfw_fname_IX
    (active, nsfw, filename) ;

-
Then we go to the downloaded table. This is more complicated. Its columns do not appear in the select list (only an aggregate result, the count) but they are used in the WHERE, GROUP BY clauses and (the computed count) in the ORDER BY / LIMIT. To complicate matters, one of the conditions is a range condition (>) and not equality:

WHERE downloaded.completed = 1
  AND downloaded.datetime > DATE_SUB(NOW(), INTERVAL 7 DAY) 
GROUP BY downloaded.file_name 
ORDER BY downloaded_count DESC LIMIT 30

If both were equalities, I'd blindly add an index on (completed, datetime, file_name) but in this case, I would first try an index on (completed, file_name, datetime), i.e. first the column from the equality check, then the column in the grouping and finally the other.

ALTER TABLE downloaded
  ADD INDEX comp_fname_dt_IX
    (completed, file_name, datetime) ;

-
Another improvement that is often possible in queries that have ORDER BY and LIMIT is to first do this in a derived table and then join the other tables. We can't really do this exactly here but we can try to put this in the derived table.

Notice that from the uploaded table only the columns in the active_nsfw_fname_IX index are used
And that for the downloaded table the columns of the comp_fname_dt_IX index are aligned with the order that queries are (logically) executed (WHERE - GROUP BY - SELECT):

( SELECT COUNT(CASE WHEN d.datetime > DATE_SUB(NOW(), INTERVAL 7 DAY) THEN 1 END)
             AS downloaded_count
       , d.file_name
  FROM downloaded AS d JOIN uploaded AS u 
    ON d.file_name = u.file_name 
  WHERE d.completed = 1
    AND u.active = 1 
    AND u.nsfw = 0 
  GROUP BY d.file_name 
  ORDER BY downloaded_count DESC LIMIT 30
)

The query finally becomes:

SELECT dc.downloaded_count
     , dc.file_name                 -- you can remove this column from the results
     , up.*                         -- as uploaded has a file_name column
FROM 
    ( SELECT COUNT(CASE WHEN d.datetime > DATE_SUB(NOW(), INTERVAL 7 DAY) THEN 1 END)
                 AS downloaded_count
           , d.file_name
      FROM downloaded AS d JOIN uploaded AS u 
        ON d.file_name = u.file_name 
      WHERE d.completed = 1
        AND u.active = 1 
        AND u.nsfw = 0 
      GROUP BY d.file_name 
      ORDER BY downloaded_count DESC LIMIT 30
    ) AS dc
    JOIN uploaded AS up
      ON dc.file_name = up.file_name 
ORDER BY downloaded_count DESC ;                 -- no need for LIMIT here

Testing time! I suggest you add these 2 indexes and then time the queries, both your original one and the one above.

And to answer your question, no it is not possible to index the aggregated column. MySQL has neither computed columns nor materialized views which is what is needed in this case (only MariaDB has implemented persistent, computed columns but that wouldn't help you in this query.)

So, the only way is to try by indexing and rewriting the query to reduce as much as possible the number of rows that will be ordered by - and that's what I essentially tried to accomplish above.

You will still see a "filesort" in the EXPLAIN but it's totally different (in terms of performance) to sort 100 than 10k rows.

StackExchange Database Administrators Q#57426, answer score: 5