4

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` int(1) NOT NULL,
  `file_name` varchar(255) NOT NULL,
  `file_size` varchar(255) NOT NULL,
  `file_ext` varchar(255) NOT NULL,
  `file_name_keyword` varchar(255) NOT NULL,
  `access_key` varchar(40) NOT NULL,
  `upload_datetime` datetime NOT NULL,
  `last_download` datetime NOT NULL,
  `file_password` varchar(255) NOT NULL DEFAULT '',
  `nsfw` int(1) NOT NULL,
  `votes` int(11) NOT NULL,
  `downloads` int(11) NOT NULL,
  `video_thumbnail` int(1) NOT NULL DEFAULT '0',
  `video_duration` varchar(255) NOT NULL DEFAULT '',
  `video_resolution` varchar(11) NOT NULL,
  `video_additional` varchar(255) NOT NULL DEFAULT '',
  `active` int(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  FULLTEXT KEY `file_name_keyword` (`file_name_keyword`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3328 ;

table: downloaded (Total 5,152.0 KiB)

CREATE TABLE IF NOT EXISTS `downloaded` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `file_name` varchar(255) NOT NULL,
  `completed` int(1) NOT NULL,
  `client_ip_addr` varchar(40) NOT NULL,
  `client_access_key` varchar(40) NOT NULL,
  `datetime` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=31475 ;
14
  • 1
    AND uploaded.nsfw = 1 would be better
    – Philᵀᴹ
    Jan 23, 2014 at 21:33
  • Hehe, thanks for jokes :) But seriously, this is first time I am facing problem like this and could not find a way of solving this. Jan 23, 2014 at 21:38
  • What do you mean indexes on a non existing column? I'm not sure I follow.
    – Zane
    Jan 23, 2014 at 21:56
  • @Zane My guess is how to index the computed column (downloaded_count) Jan 23, 2014 at 21:57
  • 1
    @WigglerJtag I suggest you add the CREATE TABLE statements for the 2 tables, so people can see the indexes you have in the other columns (I guess not many.) Jan 23, 2014 at 22:05

2 Answers 2

5

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.

0
1

Unfortunately MySQL doesn't support "Functioned Based" index (but you will have noticed that).

It is therefore not recommended to use this trick, Peter Zeitsev wrote in a blog post on the ORDER BY thematic :

Do not sort by expressions I guess this one is obvious – expressions or functions will block index usage for order by.

So my advices are :

  • Well index your other fields, especially the field used for your join.
  • Increase your sort_buffer_size and watch for an evolution

Sorry for the poor answer but i think you'll not really find better solution...

Best Regards

3

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.