Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ;
share|improve this question
1  
AND uploaded.nsfw = 1 would be better –  Phil Jan 23 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. –  Wiggler Jtag Jan 23 at 21:38
    
What do you mean indexes on a non existing column? I'm not sure I follow. –  Zane Jan 23 at 21:56
    
@Zane My guess is how to index the computed column (downloaded_count) –  ypercube Jan 23 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.) –  ypercube Jan 23 at 22:05

2 Answers 2

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.

share|improve this answer
    
Super! I really appreciate that you spent your time on writing this. I will study it tomorrow to understand at least a half of it and then post some results. Thanks again :) –  Wiggler Jtag Jan 25 at 3:09

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

share|improve this answer
    
can you share the link of the post written by Peter Zeitsev? –  ursitesion Jan 24 at 14:48
    
    
Thanks @mfouilleul... –  ursitesion Jan 24 at 14:51
    
Thanks for interesting post. –  Wiggler Jtag Jan 24 at 15:24

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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